Referencing Diff Sheet in Formula in VBA
 

Right now I have a code line in my macro which says:
Range(CellLocation).Value = "=SUBTOTAL(9,'Main'!R1C5:R200C5)"

I want to replace 'Main' with a variable name (SheetName) that represents a
sheet name. I get errors if I if replace 'Main' with SheetName or
'SheetName'. Is it possible to do what I want?

Referencing Diff Sheet in Formula in VBA
 

maybe...

Range(CellLocation).FormulaR1C1 _
= "=SUBTOTAL(9,'" & yourVariableHere & "'!R1C5:R200C5)"

If you used a variable to represent the worksheet (not the worksheet name):

Range(CellLocation).FormulaR1C1 _
= "=SUBTOTAL(9,'" & yourWksVariable.Name & "'!R1C5:R200C5)"


I also changed .value to .formular1c1.

Al wrote:

Right now I have a code line in my macro which says:
Range(CellLocation).Value = "=SUBTOTAL(9,'Main'!R1C5:R200C5)"

I want to replace 'Main' with a variable name (SheetName) that represents a
sheet name. I get errors if I if replace 'Main' with SheetName or
'SheetName'. Is it possible to do what I want?

--

Dave Peterson