This is a spinoff of the original thread: "Macro to add numbers from different worksheets (Excel 2000)"
I got GREAT answers how to use a 3d formula in contiguous sheets, but I can't guarantee I know the names of the first and last sheets and I can't guarantee they will be contiguous.
So I am trying to write a macro to total all sheets except those with a certain name as shown below. When I try to access cell B7 on sheet s, I get a compile error on two lines that says "Object Required". What am I doing wrong? Here is the macro:
a = 0
b = 0
For Each s In Worksheets
If s.Name <> "Directories" Then
If s.Name <> "Summary" Then
b = b + s.Name!B7 <-----------PROBLEM HERE
a = a + (s.Name!B7 / s.Name!B10) <--------AND HERE
End If
End If
Next s
Range("B7").Select
ActiveCell.FormulaR1C1 = b
Range("B10").Select
ActiveCell.FormulaR1C1 = a
THANKS!
I got GREAT answers how to use a 3d formula in contiguous sheets, but I can't guarantee I know the names of the first and last sheets and I can't guarantee they will be contiguous.
So I am trying to write a macro to total all sheets except those with a certain name as shown below. When I try to access cell B7 on sheet s, I get a compile error on two lines that says "Object Required". What am I doing wrong? Here is the macro:
a = 0
b = 0
For Each s In Worksheets
If s.Name <> "Directories" Then
If s.Name <> "Summary" Then
b = b + s.Name!B7 <-----------PROBLEM HERE
a = a + (s.Name!B7 / s.Name!B10) <--------AND HERE
End If
End If
Next s
Range("B7").Select
ActiveCell.FormulaR1C1 = b
Range("B10").Select
ActiveCell.FormulaR1C1 = a
THANKS!