Printing Visible Sheets in Sequence

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
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
 
Back
Top