Print Preview selected sheets

[Hudco]

I have a workbook with sheets named Control, Temp, Start, End & Tax. An active X button will copy the Temp sheet between Start & End. The number of sheets created will depend on the number of employees with one employee per sheet. Each of the sheets created from the Temp sheet is renamed with a unique name.

I am trying to create a macro which will print to preview as one run all the employee sheets. The employee sheets will already have the print area specified (same on every sheet). I know I can just hihlight all the required sheets but I am trying to automate for people with limited excel skills.

Can anyone help,
Thank you

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is any use. If you want the option to actually print after the preview then uncomment the line near the end of the code.

Sub Print_Preview_Employees()
  Dim wsAct As Worksheet
  Dim i As Long
  Dim ShtNames As String
  
  Set wsAct = ActiveSheet
  For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
    ShtNames = ShtNames & "?" & Sheets(i).Name
  Next i
  Sheets(Split(Mid(ShtNames, 2), "?")).PrintPreview
'  If MsgBox("Confirm to print?", vbYesNo) = vbYes Then ActiveWindow.SelectedSheets.PrintOut
  wsAct.Select
End Sub

 

[Hudco]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is any use. If you want the option to actually print after the preview then uncomment the line near the end of the code.

Sub Print_Preview_Employees()
  Dim wsAct As Worksheet
  Dim i As Long
  Dim ShtNames As String
 
  Set wsAct = ActiveSheet
  For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
    ShtNames = ShtNames & "?" & Sheets(i).Name
  Next i
  Sheets(Split(Mid(ShtNames, 2), "?")).PrintPreview
'  If MsgBox("Confirm to print?", vbYesNo) = vbYes Then ActiveWindow.SelectedSheets.PrintOut
  wsAct.Select
End Sub

 

[Hudco]

Thanks Peter,
Worked well.
And I updated record - didn't realise that had all changed.

Thanks,
Clyde

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your details.