Macro to set print format for workbook

[AvgMSUser]

I have a workbook with multiple worksheets set to show/hide. I've created the below macro as a start to set the printing format so that it doesn't change for the users. I have two questions: 1) will the print settings apply when the users save the document in pdf format, and 2) how do I get the macro's Array to include only the worksheets the user completed?

I've tried various ways to get the macro to reference a cell within the workbook that lists only the worksheets that need to be printed but they didn't work. I'd appreciate all guidance provided as I'm still relatively new to macros.

Sub PrintWorkbook()

Sheets(Array("Welcome!", "Summary Tab")).Select
Sheets("Welcome!").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

End Sub

 

[Mohammad Basem]

Hi AvgMSUser,

The answers:
1) Yes

2) See this link VBA to create an Array from a list

 

[AvgMSUser]

Thank you. I don't completely understand what I read but I'll play with my macro to see if I can correctly have it reference the dynamic array I have built.

 

[Mohammad Basem]

Hi AvgMSUser,

The idea is simple, use the Replace parameter of Worksheet.Select(Replace).

In the beginning set Replace = True to select the first worksheet in the array then set to False to extend the array by adding more worksheets.

Hope this is clear.

 

[AvgMSUser]

Thanks Mohammad. I'm slow as I'm still a beginner with macros but this is beginning to make more sense.

 

[AvgMSUser]

Mohammad, thanks again for your help. I wasn’t able to get the code you provided working but discovered the below code that suits my purposes. As long as the users don’t hide a worksheet they used, this code will print all of the necessary worksheets.

It is a little slow but I am assuming it is because each sheet is being checked to make sure it isn’t either “US” or “Canadian” worksheets</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>
            wSheet.PrintOut</SPAN>
    End If</SPAN>
    End If</SPAN>
End If</SPAN>
</SPAN>

 

[Mohammad Basem]

Hi AvgMSUser,

Try this,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Sub PrintMultipleSheets()

   Dim wSheet As Worksheet
   Dim bl1stSheet As Boolean

   bl1stSheet = True
   For Each wSheet In ActiveWorkbook.Worksheets
      With wSheet
         If .Visible = xlSheetVisible And .Name <> "U.S. " And wSheet.Name <> "Canadian" Then
            .Select bl1stSheet
            bl1stSheet = False
         End If
      End With
   Next wSheet
   ActiveWindow.SelectedSheets.PrintPreview
   ActiveWindow.SelectedSheets.PrintOut
   ActiveSheet.Select
   Set wSheet = Nothing

End Sub[/COLOR][/SIZE][/FONT]