VBA for Page Setup for Multiple Worksheets

[AvgMSUser]

Can someone let me know if I am taking the best approach? I have a workbook with 24 worksheets, all requiring unique bottom margins and scale settings. I need the users to print and create pdf versions of the worksheets they use with a consistent appearance so I plan to apply a Private Sub for each worksheet to trigger the formatting if the worksheet is used.
</SPAN>
This is my first time working with VBA for page setup formatting so all advice is appreciated.</SPAN>

 

[Worf]

- What Excel version are you using?
- It can be done with a single routine:

' ThisWorkbook module
' tested with Excel 2013
Dim formatted() As Boolean, i%


Private Sub Workbook_Open()
ReDim formatted(1 To Me.Worksheets.Count)
For i = 1 To Me.Worksheets.Count
    formatted(i) = 0
Next
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sn
sn = Evaluate("=sheet(""" & Sh.Name & """)")
If Not formatted(sn) Then
    Sh.PageSetup.BottomMargin = Application.InchesToPoints(1)
    Sh.PageSetup.FitToPagesWide = 1
    formatted(sn) = 1
End If
End Sub

 

[AvgMSUser]

I am using Excel 2010.

 

[AvgMSUser]

I have been doing further research to create a code that will: 1) make the Save as window appear and 2) save all visible worksheets except the ones names “U.S.” and “Canadian” as one PDF document</SPAN>
I am not skilled enough to utilize Booleans in my code so I tried to combine my simple Print code with another I found but it isn’t working. Am I completely off the mark trying to get the below code to work?</SPAN>

Sub SavePDF()</SPAN>
' Save file as pdf</SPAN>
    Dim i As Variant</SPAN>
    Dim wSheet As Worksheet</SPAN>
For Each wSheet In ActiveWorkbook.Worksheets</SPAN>
If wSheet.Visible = xlSheetVisible Then</SPAN>
    If wSheet.Name <> "U.S." Then</SPAN>
    If wSheet.Name <> "Canadian" Then</SPAN>
i = Application.GetSaveAsFilename("Last Name First Name", "PDF Files (*.pdf), *.pdf")</SPAN>
If VarType(i) = vbString Then</SPAN>
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=i, _</SPAN>
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _</SPAN>
    IgnorePrintAreas:=False, OpenAfterPublish:=False</SPAN>
    End If</SPAN>
    End If</SPAN>
End If</SPAN>
   
End Sub</SPAN>

 

[Worf]

See if this does what you want:

Sub Some_to_PDF()
Dim sarr, i%, j%, sh As Worksheet
ReDim sarr(0 To ThisWorkbook.Worksheets.Count - 3)  ' array of sheet names
j = 0
For i = 1 To ThisWorkbook.Worksheets.Count
    Set sh = Sheets(i)
    If sh.Name <> "U.S." And sh.Name <> "Canadian" And sh.Visible Then
        sarr(j) = sh.Name
        j = j + 1
    End If
Next
Sheets(sarr).Select     ' only desired sheets
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\avg.pdf"
Sheets(1).Activate
End Sub

 

[AvgMSUser]

Hi Worf,

I tried the code but received the error "Runt-time error '9': Subscript out of Range". The error appears to be occuring at row: Sheets(sarr).Select ' only desired sheets.

Thanks for continuing to help me with this.

 

[Worf]

Hi

That code worked for me. Please test this other one, replacing the sheet names where indicated.
You can use any number of sheets on that line, up to a reasonable limit.

Sub Typing_Names()
Sheets(Array("Sheet1", "Sheet2")).Select    ' your sheet names here
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\avgtest.pdf"
End Sub