I want to make a sum spanning multiple sheets using the Index Numbers of the other worksheets (4 to 13) as reference instead of their names.
I chose to use index number because the names of the sheets will change but the Index numbers won’t.
I tried to do this:
Range("Y9") = "=SUM('Sheets(4):Sheets(13)'!R2C40)"
This just took "Sheets(4)" and "Sheets(13)" as names instead of refering to the concerned sheets
I also tried putting quotes:
Range("Y9") = "=SUM('" & Sheets(4) & ":" & Sheets(13) & "'!R2C40)"
However this didn’t work either and I get the 438 error code.
This refer to an error of available property/method.
I wonder is it possible to use Index Numbers like that.
>Solution :
Sheets(4) returns a sheet object.
You need to provide the Name of the sheet as a string:
Range("Y9").FormulaR1C1 = "=SUM('" & Sheets(4).Name & ":" & Sheets(13).Name & "'!R2C40)"
One note: it is always best practice to use the parent sheet when referring to range objects. That way you are not dependent on what is the active sheet nor do you need to activate a sheet.
Also the use of Sheets over WorkSheets can cause issues as Sheets includes code and graph sheets as well. So if you mean WorkSheets use that.
ie
Worksheets("blahblah").Range("Y9").FormulaR1C1 = "=SUM('" & WorkSheets(4).Name & ":" & WorkSheets(13).Name & "'!R2C40)"