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.
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