Can VBA be used to set print area as a formula in a sheet?

Question

I am using VBA to create sheets from a template and then creating named ranges scoped for each sheet created. Each named range corresponds to the print area that would be a page when printed. I also use the sheet index to create a string that is unique to each sheet for the named ranges so they are easier to troubleshoot since there could be up to 2000 sheets.

Sub NewSheet()

Dim n As String, pr1 As String, pr2 As String, pr3 As String, NmSTR As String
Dim Print_Area_1 As Range, Print_Area_2 As Range, Print_Area_3 As Range

With ThisWorkbook
    NmSTR = ActiveSheet.Name
    n = ActiveSheet.Index
    pr1 = "Print_Area_" & n & "1"
    pr2 = "Print_Area_" & n & "2"
    pr3 = "Print_Area_" & n & "3"
    Set Print_Area_1 = Range("$A$1:$R$80")
    Set Print_Area_2 = Range("$A$85:$R$164")
    Set Print_Area_3 = Range("$A$170:$R$249")
    Worksheets(NmSTR).Names.Add Name:=pr1, RefersTo:=Print_Area_1
    Worksheets(NmSTR).Names.Add Name:=pr2, RefersTo:=Print_Area_2
    Worksheets(NmSTR).Names.Add Name:=pr3, RefersTo:=Print_Area_3
End With

End Sub

Since this workbook will be filled out and printed from a tablet (macros will be disable when using), I have a formula in cell AA1 that analyzes if anything has been entered on the 2nd or 3rd page and returns either a 1, 2, or 3 for which option in the print area formula it should use. I want to use VBA code to set the print area to the following formula when the sheet is created so it can update throughout the week as information gets entered.
=CHOOSE('Sheet1'!$AA$1,'Sheet1'!Print_Area_11,('Sheet1'!Print_Area_11,'Sheet1'!Print_Area_12),('Sheet1'!Print_Area_11,'Sheet1'!Print_Area_12,'Sheet1'!Print_Area_13))

I tried adding the following VBA code to set the print area but the result was the value of Print_Area_1 'Sheet1'!$A$1:$R$80 and not the formula.

With ActiveSheet.PageSetup
    .PrintArea = "=CHOOSE('" & NmSTR & "'!$AA$1,'" & NmSTR & "'!" & pr1 & ",('" & NmSTR & "'!" & pr1 & ",'" & NmSTR & "'!" & pr2 & "),('" & NmSTR & "'!" & pr1 & ",'" & NmSTR & "'!" & pr2 & ",'" & NmSTR & "'!" & pr3 & "))"
End With

Any help will be appreciated.

Answer 1

I found I was almost on the right track with my approach for this problem. Although the Print Area property does not allow setting it as a formula, once created, you can change it with the Names object .RefersToLocal property to make it a formula.

Sub NewSheet()

Dim n As String, pr1 As String, pr2 As String, pr3 As String, NmSTR As String
Dim Print_Area_1 As Range, Print_Area_2 As Range, Print_Area_3 As Range

With ThisWorkbook
    NmSTR = ActiveSheet.Name
    n = ActiveSheet.Index
    pr1 = "Print_Area_" & n & "1"
    pr2 = "Print_Area_" & n & "2"
    pr3 = "Print_Area_" & n & "3"
    Set Print_Area_1 = Range("$A$1:$R$80")
    Set Print_Area_2 = Range("$A$85:$R$164")
    Set Print_Area_3 = Range("$A$170:$R$249")
    Worksheets(NmSTR).Names.Add Name:=pr1, RefersTo:=Print_Area_1
    Worksheets(NmSTR).Names.Add Name:=pr2, RefersTo:=Print_Area_2
    Worksheets(NmSTR).Names.Add Name:=pr3, RefersTo:=Print_Area_3
    ActiveSheet.PageSetup.PrintArea = "$A$1"
    ActiveSheet.Names("Print_Area").RefersToLocal = "=CHOOSE('" & NmSTR & "'!$AA$1,'" & NmSTR & "'!" & pr1 & ",('" & NmSTR & "'!" & pr1 & ",'" & NmSTR & "'!" & pr2 & "),('" & NmSTR & "'!" & pr1 & ",'" & NmSTR & "'!" & pr2 & ",'" & NmSTR & "'!" & pr3 & "))"
End With
End Sub