Coding a formula with dynamic sheet name

[jknight291]

I am writing a code that automates an annual work process that involve copying several worksheets and changing formulas/macros. I am stuck with trying to code a formula VLOOKUP that references a newly made worksheet. When I run the macro, it doesn't not recognize "tableyear" as a variable and the formula returns as NA. The problem is the VLOOKUP at the bottom.

Can anyone help?

Sub createnewtable()

Dim tableprevyear As String
Dim tableyear As String
Dim tablenewyear As String

'------------------------------------------------------------------
'Copy Previous Year Tables

    'Copy Monthly Table

        tableprevyear = ActiveSheet.Range("W1").Value

        tableyear = ActiveSheet.Range("W1").Value + 1

        tablenewyear = "MonthlyTable" & tableyear

        Sheets("MonthlyTable" & tableprevyear).Copy before:=Sheets("MonthlyTable" & tableprevyear)

        ActiveSheet.Name = tablenewyear

    'Copy & Prepare Train 1 Data Sheet

        Sheets("Train1" & tableprevyear).Copy after:=Sheets(tablenewyear)

        ActiveSheet.Name = "Train1" & tableyear
       
        ActiveSheet.Range("A2:Z400").ClearContents

    'Copy & Prepare Train 2 Data Sheet

        Sheets("Train2" & tableprevyear).Copy after:=Sheets("Train1" & tableyear)

        ActiveSheet.Name = "Train2" & tableyear
       
        ActiveSheet.Range("A2:Z400").ClearContents

    'Copy Train 1 BDP Data Sheet

        Sheets("Train1BDP" & tableprevyear).Copy after:=Sheets("Train2" & tableyear)
   
        ActiveSheet.Name = "Train1BDP" & tableyear
       
        Train1BDPShtNm = ActiveSheet.Name
       
    'Copy Train 2 BDP Data Sheet

        Sheets("Train2BDP" & tableprevyear).Copy after:=Sheets("Train1BDP" & tableyear)

        ActiveSheet.Name = "Train2BDP" & tableyear
       

'------------------------------------------------------------------
'Deactivate Previous Year Monthly Table

    Sheets("MonthlyTable" & tableprevyear).Activate
    ActiveSheet.Range("B5:N5").Copy
    ActiveSheet.Range("B5").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

'------------------------------------------------------------------
'Converting New Table Formulas to New Year

    Sheets(tablenewyear).Activate
   
    ActiveSheet.Range("W1").Value = tableyear
   
    FirstDayofYear = ActiveSheet.Range("C7").Value
   
   

'------------------------------------------------------------------
'Exract First Line of Data

    ThisWorkbook.Sheets("Train1" & tableyear).Activate

   
    'the next lines fill in pertinent formulas

Range("O2").Formula = "=VLOOKUP(F2,INDIRECT(""Train1BDP""&tableyear&""!$A$1:$B$90""), 2, False)"
   

End Sub

 

[severynm]

When I run the macro, it doesn't not recognize "tableyear" as a variable and the formula returns as NA

It looks like you're not escaping the quotations quite right. Notice how the VBA compiler does not auto format the line like it normally would with variables. Your string should be
"=VLOOKUP(F2,INDIRECT(""Train1BDP"""&tableyear&"""!$A$1:$B$90""), 2, False)". Two quotes to escape a single quote, and one more to start/end the string for a total of 3.

 

[mikerickson]

Have you tried using
ThisWorkbook.Sheets("Train1BDP" & tableyear).Range("$A$1:$B$90").Address(True, True, xlA1, True)

instead of the INDIRECT