Print visible sheets - ordering

[Darin Kramer]

Hi Guys,

I have simple VB that prints all sheets in my workbook - see below.
Problem is that the order that it prints the sheets is not the way they
are visible - For example it prints Appendix G before Appendix A, yet A
is visible before G. To complicate Matters there are hidden sheets
(which it ignores). So question, how can I tell Excel the order of
sheets to print, or just to print them as they are visible, instead of
when they were created? (I'M assuming appendix G was created before
appendix A)

Regards

D

Sub PrintVisibleSheets_2()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
If ws.Name <> "Grading_guid" Then
ws.PrintOut Copies:=1, Collate:=True
End If
End If
Next
End Sub

*** Sent via Developersdex http://www.developersdex.com ***

 

[Martin Fishlock]

Hi Darin:

Try using a for loop and stepping through the serquence as in the following

Sub PrintVisibleSheets_2()

dim lShtNr as long
dim ws as worksheet
with ActiveWorkbook
For lShtNr= 1 to .Worksheets.count
set ws = .worksheets(lShtNr)
If ws.Visible = xlSheetVisible and ws.Name <> "Grading_guid" Then
ws.PrintOut Copies:=1, Collate:=True
End If
Next lShtNr
End With
End Sub

 

[Darin Kramer]

WORKS PERFECTLY - THANKS!

*** Sent via Developersdex http://www.developersdex.com ***