SUM formula, target is in a sheet with a variable name

Hi,

I have what is certainly a basic problem, but I am stuck.
In the first sheet "Sheet1", I am trying to SUM the values of column C from another sheet 'XXX'. The trick is that the name of this other sheet is a variable, like Sheets(1+i).name , "i" being an integer. I can't find the proper syntax for the formula below.

Any help would be appreciated.
Thank you!

PHP Code: 

ActiveCell.FormulaR1C1 = "=SUM(XXX!C[1])" 


Thank you for the help!

Unfortunately, this solution doesn't work. I think that I have tried every combination of ' " "" """ &, with no success (Thank you for explaining the reason of the ', I didn't know that). I have retried yours, and even if it compiles, I get a Run-time error 438: Object doesn't support this property or method when I activate the sub.

I have attached the relevant part of my current project. It is not very nice after I have removed whatever should not be here, but I hope that it is clear enough.

-Only the 2 first sheets are required; just press the orange button in sheet "Export".
-In module 1, the problematic part is at the end, between the two '++++++++ lines. You can also toggle the 2 ActiveCell lines by toggling the '

Thank you again!

Test.xlsm

Taking a look at my post I realized that it is wrong. I haven't looked at your file yet but if you try this correction it may solve it. When I get more time later I'll take a closer look.

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

Taking a look at my post I realized that it is wrong. I haven't looked at your file yet but if you try this correction it may solve it. When I get more time later I'll take a closer look.

Please Login or Register  to view this content.

slight change

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thank you to both of you. I had some trouble to adopt it to my project, but it is finally working.

Nevertheless, if you won't mind to devote more of your time to this issue, I would have another related question. When I don't use the relative reference (ie C[1]) but the reference of the column, I get weird things instead.

PHP Code: 

    ActiveCell.FormulaR1C1 = "=SUM('" & Sheets(2 + i).Name & "'!E:E)"                        
    ActiveCell.FormulaR1C1 = "=SUM('" & Sheets(1).Range("A" & 2 + i).Value & "'!E:E)" 


both return

=SUM('AAA'!E:(E))

What would be the correct syntax to get SUM('AAA'!E:E) as expected?
Thank you again!

ThirtyTwo, no need to quote an entire post to respond to it, adds a lot of volume to the thread. Especially true when your response is immediately after the post you are responding to (see rule #12). Thanks.

Thank you again to both of you, it is very helpful