Printing certain pages

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A rather simple problem but I can't find what I want in help.

I wish to loop through each sheet in the workbook and print those called WIP*

I was think of somethig like:

Private Sub PrintWIPButton_Click()

Dim SheetNum As Integer

For SheetNum = 1 To Worksheets.Count

If Sheets(SheetNum).Name Like "WIP*" Then

!!!! Add to a collection (or array?) of sheets !!!!

End If

Next SheetNum

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Worksheets(1).Select

End Sub

---------------------------------

I'm just having problems in defining a collection of sheets.

Can anyone fill in the missing line of code??

TIA!

Nick Shinkins
 
Hi Nick

One way

Sub Print_Worksheets()
Dim sh As Worksheet
Dim arr() As String
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Worksheets
If Left(sh.Name, 3) = "WIP" Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = sh.Name
End If
Next
With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select
End With
End Sub
 
try this. Change to PrintOut after testing

Sub printwip()
For Each ws In Worksheets
If LCase(Left(ws.Name, 3) = "wip") Then ws.PrintPreview
Next
End Sub
 
Nick
Use something like this:
Sub PrintWIP()
Dim ws as WorkSheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name="WIP*" Then
'Or If Left(ws.Name,3) = "WIP" Then
With ws
'Put your print command here
End With
End Sub
HTH Otto
 
Private Sub PrintWIPButton_Click()
Dim bReplace As Boolean
Dim SheetNum As Integer
bReplace = True
For SheetNum = 1 To Worksheets.Count

If Sheets(SheetNum).Name Like "WIP*" Then
Sheets(SheetNum).Select bReplace
bReplace = False
End If

Next SheetNum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Worksheets(1).Select

End Sub
 
Use this one with error checking


Sub Print_Worksheets()
Dim sh As Worksheet
Dim arr() As String
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Worksheets
If Left(sh.Name, 3) = "WIP" Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = sh.Name
End If
Next
If N > 0 Then
With ThisWorkbook
.Worksheets(arr).PrintOut
.Worksheets(1).Select
End With
End If
End Sub
 
Thanks for all your replies!

I think I will use Tom's method combined with Ron's error checking in case
there are no sheets with WIP (which is unlikely in the context).

I guess it will be quicker not to flood the printer with individual requests
for each page and avoiding arrays (which I thought would be the only possible
way) is preferable.

I'm never quite sure why you dedicate so much of your time to helping others
but it is invaluable when the books and help files just don't seem to cover
your problem.

Thanks again guys!

Nick Shinkins
 
The easiest error checking would be:

Private Sub PrintWIPButton_Click()
Dim bReplace As Boolean
Dim SheetNum As Integer
bReplace = True
For SheetNum = 1 To Worksheets.Count

If Sheets(SheetNum).Name Like "WIP*" Then
Sheets(SheetNum).Select bReplace
bReplace = False
End If

Next SheetNum
if not bReplace then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End if
Worksheets(1).Select

End Sub

bReplace will be false if at least one sheet has been selected in the loop
(at least one sheet starts with WIP).
 
Back
Top