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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Nick Shinkins" wrote in message ...
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

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

--
Don Guillett
SalesAid Software

"Nick Shinkins" wrote in message
...
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

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
"Nick Shinkins" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"Nick Shinkins" wrote in message
...
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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Nick Shinkins" wrote in message
...
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

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).

--
Regards,
Tom Ogilvy


"Nick Shinkins" wrote in message
...
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