refer to a sheet in a macro

[ultratch47]

here is macro:
Range("C12").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-11]C[3]"
Range("D12").Select
ActiveCell.FormulaR1C1 = "=(Sheet3)!R[-11]C[2]"
Range("B14").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-13]C[6]"
Range("B15").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-14]C[7]"

the sheet3 in parenthesis will not compute,i would like to reference the 3rd sheet in the workbook, (the name of this sheet will never be the same)
how should htis be written correctly to refer to Sheet3?

here are my sheet names:
Sheet1 (sheet1)
Sheet2 (sheet2)
Sheet3 (test)

thanks

 

[Andrew Poulsom]

Like this:

ActiveCell.FormulaR1C1 = "=" & Sheet3.Name & "!R[-11]C[2]"

 

[just_jon]

Try --

Dim ThirdSheet as String
ThirdSheet = Sheets(3).Name

...

ActiveCell.FormulaR1C1 = "=" & ThirdSheet & "!R[-13]C[6]"

Untested...

 

[ultratch47]

Andrew, i got the same error, not sure why

Jon, yours works but i got this as the final formula in the cell:
=test!J65535
i need it to equal:
=test!F1

it may be because i dont understand that line ("!R[-11]C[2]"a) as to why i cant figure it out.. sorry for the ignorance.
thanks

 

[Andrew Poulsom]

It worked when I tested it.

To get the code for the last bit turn on the macro recorder and enter the formula manually. Make sure you use proper relative and absolute references. Turn off the recorder an look at the code.

Or you could use the Formula property:

ActiveCell.Formula = "=" & Sheet3.Name & "!F1"

 

[just_jon]

I don''t like R1C1 much; try

ActiveCell.Formula = "=" & ThirdSheet & "!F1"

 

[ultratch47]

thanks, works great

i dont like the R1C1 thingy either, your way is much smoother...