How to reference a cell value in a different worksheet using sheet index numbers in VBA?

[brianhf]

Hello everyone,

I apologize if my question has already been answered, but I can't seem to find a solution to this issue.

I want a simple command/formula that reads a value in a specific cell in a different worksheet (within the same workbook) and then multiplies it with 24. I don't know the name of the worksheet in advance, so I'm trying to use the index number of the worksheet instead of the name of the worksheet.

When I use the 'Record macro' button on an existing worksheet (named 20-04-21) I will get this code, and it will work perfectly:

ActiveCell.FormulaR1C1 = "='20-04-21'!R[31]C[1]*24"

But how do I change it so that it reads the value from their index names Worksheet(2), Worksheet(3) etc. instead?

I've tried various things such as:

ActiveCell.FormulaR1C1 = "='WorkSheets(2)'!R[31]C[1]*24"
ActiveCell.FormulaR1C1 = "='Sheets(2)'!R[31]C[1]*24"
ActiveCell.FormulaR1C1 = "=WorkSheets(2)!R[31]C[1]*24"

None of them seem to work. What do I need to write instead?

Thanks in advance!

 

[aRandomHelper]

Welcome to the forum!

Try:

ActiveCell.FormulaR1C1 = "='" & Sheets(2).Name & "'!R[31]C[1]*24"

 

[brianhf]

Welcome to the forum!

Try:

ActiveCell.FormulaR1C1 = "='" & Sheets(2).Name & "'!R[31]C[1]*24"

Wow, I've been trying for several hours now and you just fixed my issue within 5 minutes.

You're a treasure - thank you so much!

Have a great day