Selecting sheets that have names begining with?

[avayaman]

I want to write amacro to print out all sheets in a workbook that have a certain name. In this case all the sheets in question are copies of one another, example

"Sheet1 (2)", "sheet1 (3)", "sheet1 (4)" and so on. I can't figure out how to write a macro just to select those sheets and no others. Right now it just searches for Sheet1 (2) through sheet1 (100) skipping over any missing ones with an on error resume next statement. This works, but it seems like a waste of code space. Trying to replace the number in brackets with a wildcard and using a for next loop doesn't work.

 

[ploeryan]

Hello

try using the following - obvious you can adjust the Sheet1* to suit your needs:

For Each ws In Worksheets
If ws.Name Like "Sheet1*" Then ws.PrintOut
Next ws

Regards

 

[avayaman]

Sounds good, I'll give it a go.

 

[avayaman]

OK, It works real good, but I need to take it a step further if possible. As a bit of background, this is a routine to extract programing information out of Telephone PBX worksheets & automatically produce set lables for individual sets. I end up with all these sheets labeled "setlable (2)", "setlable (3)", etc. The original "setlable is is the blank template and is re-hidden after each sheet is produced. Because these sheets undergo further optional formatting (EG logo insertion)when they print out. I need to send each sheet into a subroutine before they print. Therefore using a statement to print all visible sheets en masse does not work too well. I need some means to grab each sheet Visible sheets whose name starts with "setlable" one at at time, & print each sheet until all visible sheets have been printed.

 

[avayaman]

OK, It works real good, but I need to take it a step further if possible. As a bit of background, this is a routine to extract programing information out of Telephone PBX worksheets & automatically produce set lables for individual sets. I end up with all these sheets labeled "setlable (2)", "setlable (3)", etc. The original "setlable is is the blank template and is re-hidden after each sheet is produced. Because these sheets undergo further optional formatting (EG logo insertion)when they print out. I need to send each sheet into a subroutine before they print. Therefore using a statement to print all visible sheets en masse does not work too well. I need some means to grab each Visible sheet whose name starts with "setlable" one at at time, & print each sheet until all visible sheets have been printed.

 

[combo]

Some time ago I used this routine:

[tt]' declare new collection
Dim shs_col As New Collection

' add all sheets you wish to it (here - sh)
shs_col.Add Item:=sh

' select first sheet in collection
shs_col(1).Select

' select other sheets from collection, add selection
For Each sh In shs_col
sh.Select Replace:=False
Next[/tt]

And now you can print them all at once (selection)

Combo

 

[ploeryan]

Hello

if the worksheets are hidden then you can try:

For Each ws In Worksheets
If ws.Visible Then
If ws.Name Like "Sheet1*" Then ws.PrintOut
End If
Next ws

if they are very hidden you can try:

For Each ws In Worksheets
If ws.Visible=xlVeryHidden Then
If ws.Name Like "Sheet1*" Then ws.PrintOut
End If
Next ws

 

[ploeryan]

Sorry

For hidden it should read:
If ws.Visible=false Then
If ws.Name Like "Sheet1*" Then ws.PrintOut
End If