Printing Visible Sheets in Sequence

[John]

I would like to print all of the visible sheets within one
workbook with sequential page numbers. How could this
macro be modified so that only visible sheets are loaded
into the array? Thanks for your help.

Sub PrintSheets()
Dim ShtList() As String
Dim ShtCnt As Long
ReDim ShtList(1 To ActiveWorkbook.Sheets.Count)
For ShtCnt = LBound(ShtList) To UBound(ShtList)
ShtList(ShtCnt) = ActiveWorkbook.Sheets(ShtCnt).Name
Next ShtCnt
ActiveWorkbook.Sheets(ShtList).PrintOut
End Sub

 

[Ron de Bruin]

Hi John

Try this

Sub test()
Dim ws As Worksheet
Dim arr() As String
Dim N As Integer
N = 0
For Each ws In ThisWorkbook.Sheets
If ws.Visible = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ws.Name
End If
Next
ThisWorkbook.Worksheets(arr).PrintOut
Worksheets(1).selct
End Sub

 

[Ron de Bruin]

Typo < Worksheets(1).Select>

Sub test()
Dim ws As Worksheet
Dim arr() As String
Dim N As Integer
N = 0
For Each ws In ThisWorkbook.Sheets
If ws.Visible = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ws.Name
End If
Next
ThisWorkbook.Worksheets(arr).PrintOut
Worksheets(1).Select
End Sub

 

[John]

Ron, thank you - this works great! Appreciate your help.