3D formula fill in problem

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

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



>-----Original Message-----
>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
>.
>

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

--
Regards,
Tom Ogilvy


"Petr" <[email protected]> wrote in message
news:[email protected]...
> 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