Referencing a Worksheet in a Formula That's Always Changing in VBA

Question

I am trying to write a macro that copies data from another worksheet. I am having troubles on how to properly input the worksheet name in a formula. The Summary worksheet is the destination and the 5th worksheet, which will change daily (and is in format x.xx_1), is the source. Here's my code:

 Sub steadf()

 Dim SN As String

 SN = InputBox("Enter Tab Date - 2.24, 10.24, etc.")
 Worksheets(5).Name = SN & "_1"

    Sheets("Summary").Select
    Range("D24").Select
    ActiveCell.Offset(0, 3).Formula = "=SN" & "_1" & "!" & "Cost"

End Sub

When I run this, the formula in G24 is

=SN_1!Cost

The formula I'm looking to use is ='2.24_1'!Cost. I would appreciate any help. Thanks in advance.

Answer 1

Try this:

Formula = "='" & SN & "'!Cost"