Sleeper: Circular reference in a macro

[justakid]

HI Im new here and a bit of a novice when it comes to macros in excel.

i have a strange problem...
I created a macro for a report that I do every month however the data slightly changes (obviously!!).

Some months there are some departments and other times there isnt.
The macro that I have created pulls the raw data from the system and then seperates each department into seperate tabs and then puts totals at the bottom of the sheet.

The macro works great if there is data for all the departments, however when there is no data for a department the macro crashes and I get a circular reference.

any suggestions?

thanks
Samuel

[Bob Phillips]

Post the code?

[johnske]

Hi Samuel,

Welcome to VBAX.

The macro requires an error handling procedure in it. We'd need to see the macro for that. Can you paste the code please so someone can assist you?

Regards,
John

[justakid]

yeah sorry i should've posted the code when I started the thread

'Filtering Branded Thermals
    Selection.AutoFilter Field:=1, Criteria1:="BT"
    Range("A1").CurrentRegion.Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Name = "Branded Thermals"
    Range("A1").Select
    ActiveSheet.Paste
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    'Produce totals
    Range("A1").CurrentRegion.Select
    rnum = Selection.Rows.Count
    Range(Cells(rnum + 1, 11), Cells(rnum + 1, 13)).FormulaR1C1 = "=SUM(R[-1]C:R[-" & rnum - 1 & "]C)"
    Range("A1").Select
    Range("N1").End(xlDown).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Selection.Style = "Percent"
    Range(Cells(rnum + 1, 11), Cells(rnum + 1, 14)).Activate
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Range("A2").Select

[Bob Phillips]

One shot

Selection.AutoFilter Field:=1, Criteria1:="BT"
    Range("A1").CurrentRegion.Copy
    Sheets.Add
    ActiveSheet.Name = "Branded Thermals"
    Range("A1").Select
    ActiveSheet.Paste
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Cells.EntireColumn.AutoFit
     'Produce totals
    Range("A1").CurrentRegion.Select
    If rnum > 1 Then
        rnum = Selection.Rows.Count
        Range(Cells(rnum + 1, 11), Cells(rnum + 1, 13)).FormulaR1C1 = "=SUM(R[-1]C:R[-" & rnum - 1 & "]C)"
        With Range("N1").End(xlDown).Offset(1, 0)
            .FormulaR1C1 = "=RC[-1]/RC[-2]"
            .Style = "Percent"
        End With
        With Range(Cells(rnum + 1, 11), Cells(rnum + 1, 14))
            With .Interior
                .ColorIndex = 6
                .Pattern = xlSolid
            End With
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .ShrinkToFit = False
            .MergeCells = False
            .Font.Bold
        End With
    End If

[justakid]

XLD,
Thanks it works!!
One slight problems - The totals at the bottom of each tab doesnt work. Ive tied to fix it however I cant get it to work - your help will be greatly appreciated

thanks
Samuel

[Bob Phillips]

One slight problems - The totals at the bottom of each tab doesnt work. Ive tied to fix it however I cant get it to work - your help will be greatly appreciated

In what way do they not work.

One problem I had was that I couldn't get the code to run, although I did get the circular reference, presumably because of data. Can you post a sample workbook as well as saying what is wrong?

[justakid]

XLD,
I really appreciate your help with this
Have you got an email address that I can send you a copy of my workbook.
I have changed the data as it is work related and I dont think they would approve having data on the net!!

thanks
Samuel

[Bob Phillips]

XLD,
I really appreciate your help with this
Have you got an email address that I can send you a copy of my workbook.
I have changed the data as it is work related and I dont think they would approve having data on the net!!

You can try

xld dot bob at gmail dot com (do the obvious with it)

but I don't know when I can get to it. I am going to the States tomorrow, so I will travelling all day, then I have meetings, but give all the details of the problem and what you are trying to do and I will try.

.

[justakid]

XLD,

Thanks
I have emailed you the details
much appreciated
Samuel