3D formula fill in problem

[Petr]

Hallo,
I have following problem:

I would need to input variables "Front_test_subagreg" and
"Back_test_subagreg" (actually worksheets names previously selected)
in the 3D formula bellow (sum accross multiple sheets):

Sub test
Front_sh = Front_test_subagreg
Back_sh = Back_test_subagreg

ActiveCell.FormulaR1C1 = "='SUM (" & Front_sh & ":" & Back_sh &
"'!RC)"

End Sub

Thanks a lot in advance for any suggestions.
Petr Duzbaba

 

[Markus Scheible]

Dobry dzien Petr,

if I understood you right, you want the worksheet names as
a variable?

e.g. wshname1 = Worksheets(1).Name

this gives you the Name of the first worksheet within the
workbook.

Best

Markus

 

[Tom Ogilvy]

Shouldn't it be:

Sub test
Front_sh = "Front_test_subagreg"
Back_sh = "Back_test_subagreg"

ActiveCell.FormulaR1C1 = "=SUM('" & Front_sh & _
":" & Back_sh & "'!RC)"

End Sub

(note that I adjusted the formula as well)

That should work if the actual name of the sheet is Front_test_subagreg and
Back_test_subagreg.

If those are global variables set somewhere else then it would be

Sub test
Front_sh = Front_test_subagreg
Back_sh = Back_test_subagreg

ActiveCell.FormulaR1C1 = "=SUM('" & Front_sh & ":" & Back_sh &
"'!RC)"

End Sub