BA Code - Set print area on selected WS from a range of names

[thetcispaniard]

Hi,

Almost there but not quite! Bit of a newbie on the VBA besides what I have gleaned off this site and others.

Goal - set a consistent print range across multiple worksheets.

The issue I have is that it doesn't seem to work lol.

So far - I have defined the worksheet names on the 'Input' WS in range B22-B35 and tried to set the print area using the following code:

Sub Set_Lead_Sheet_Print_Area()
Dim sPrintArea As String
Dim rng As Range
Dim WS As Worksheet Application.ScreenUpdating = False
For Each rng In Sheets("Inputs").Range("B22:B35")
If Trim(rng.Value) <> "" Then
On Error Resume Next
Set WS = Nothing
Set WS = Sheets(rng.Value)
On Error GoTo 0
If WS Is Nothing Then
MsgBox "Sheet " & rng.Value & " does not exist"
Else
sPrintArea = "B1:W72"
End If
End If
Next rng
End Sub

Thanks in advance.
CM

 

[yky]

See if this works:

Hi,

Almost there but not quite! Bit of a newbie on the VBA besides what I have gleaned off this site and others.

Goal - set a consistent print range across multiple worksheets.

The issue I have is that it doesn't seem to work lol.

So far - I have defined the worksheet names on the 'Input' WS in range B22-B35 and tried to set the print area using the following code:

Sub Set_Lead_Sheet_Print_Area()
Dim sPrintArea As String
Dim rng As Range
Dim WS As Worksheet Application.ScreenUpdating = False
For Each rng In Sheets("Inputs").Range("B22:B35")
If Trim(rng.Value) <> "" Then
On Error Resume Next
Set WS = Nothing
Set WS = Sheets(rng.Value)
On Error GoTo 0
If WS Is Nothing Then
MsgBox "Sheet " & rng.Value & " does not exist"
Else
sPrintArea = "B1:W72"
WS.PageSetup.PrintArea = sPrintArea
End If
End If
Next rng
End Sub

Thanks in advance.
CM