I have a project where the resultant page tabs are variable in number and the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N

Try something like this

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "source data" Or sh.Name = "Sheet1" Then
' Do Nothing
Else
sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AlanN" wrote in message ...
I have a project where the resultant page tabs are variable in number and the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N

Sorry ... one other thing. One sheet not to print is called sheet1, but the other sheet not to print has the same name as the workbook name, how would I change "source data" to the workbook name?

Thanks Again,

Alan
"Ron de Bruin" wrote in message ...
Try something like this

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "source data" Or sh.Name = "Sheet1" Then
' Do Nothing
Else
sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AlanN" wrote in message ...
I have a project where the resultant page tabs are variable in number and the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If lcase(sh.Name) = lcase(thisworkbook.name) Or lcase(sh.Name) = "sheet1" Then
' Do Nothing
Else
sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub

You shouldn't need to replace 'Do nothing as it is a comment, not code.

Regards,
Tom Ogilvy


"AlanN" wrote in message ...
Sorry ... one other thing. One sheet not to print is called sheet1, but the other sheet not to print has the same name as the workbook name, how would I change "source data" to the workbook name?

Thanks Again,

Alan
"Ron de Bruin" wrote in message ...
Try something like this

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "source data" Or sh.Name = "Sheet1" Then
' Do Nothing
Else
sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AlanN" wrote in message ...
I have a project where the resultant page tabs are variable in number and the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N

Alan,
try this

Sub Macro2()
Sheets(1).Activate
For i = 1 To Sheets.Count
Select Case Sheets(i).Name

Case Is = "Sheet1"

Case Is = "source data"

Case Else
Sheets(i).Activate
ActiveWindow.SelectedSheets.PrintPreview
End Select
Next i
End Sub

Cecil
"AlanN" wrote in message ...
I have a project where the resultant page tabs are variable in number and the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N

See Tom's answer Alan

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AlanN" wrote in message ...
I'm very green, sickly green, perhaps :(, with VBA.

I have tried this and nothing happens ... do I need to put something in place of " do nothing" ?

Thanks, Alan
"Ron de Bruin" wrote in message ...
Try something like this

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "source data" Or sh.Name = "Sheet1" Then
' Do Nothing
Else
sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AlanN" wrote in message ...
I have a project where the resultant page tabs are variable in number and the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N

When I run this exact code in step mode, it skips from line 5 to 9 then 10,11 and finally 12.

There is no looping, it doesn't appear that it ever even looks to line 7 and 8. I have made sure that there are other pages not meeting the names I don't want printed.

Any ideas?

Thanks, Alan
"Tom Ogilvy" wrote in message ...
1Sub test()
2 Dim sh As Worksheet
3 Application.ScreenUpdating = False
4 For Each sh In ThisWorkbook.Worksheets
5 If lcase(sh.Name) = lcase(thisworkbook.name) Or lcase(sh.Name) = "sheet1" Then
6 ' Do Nothing
7 Else
8 sh.PrintOut
9 End If
10 Next
11 Application.ScreenUpdating = True
12 End Sub

You shouldn't need to replace 'Do nothing as it is a comment, not code.

Regards,
Tom Ogilvy


"AlanN" wrote in message ...
Sorry ... one other thing. One sheet not to print is called sheet1, but the other sheet not to print has the same name as the workbook name, how would I change "source data" to the workbook name?

Thanks Again,

Alan
"Ron de Bruin" wrote in message ...
Try something like this

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "source data" Or sh.Name = "Sheet1" Then
' Do Nothing
Else
sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AlanN" wrote in message ...
I have a project where the resultant page tabs are variable in number and the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N

It only looks at worksheets.

I ran this on a 4 sheet workbook:

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If LCase(sh.Name) = LCase(ThisWorkbook.Name) _
Or LCase(sh.Name) = "sheet1" Then
' Do Nothing
Debug.Print "Not Printing: " & sh.Name
Else
Debug.Print "Printing: " & sh.Name
' sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub


and it produced:

Not Printing: Sheet1
Printing: sheet4
Printing: Sheet2
Not Printing: Book2
Printing: Sheet3


Regards,
Tom Ogilvy

"AlanN" wrote in message
...
When I run this exact code in step mode, it skips from line 5 to 9 then
10,11 and finally 12.

There is no looping, it doesn't appear that it ever even looks to line 7 and
8. I have made sure that there are other pages not meeting the names I don't
want printed.

Any ideas?

Thanks, Alan
"Tom Ogilvy" wrote in message
...
1Sub test()
2 Dim sh As Worksheet
3 Application.ScreenUpdating = False
4 For Each sh In ThisWorkbook.Worksheets
5 If lcase(sh.Name) = lcase(thisworkbook.name) Or lcase(sh.Name) =
"sheet1" Then
6 ' Do Nothing
7 Else
8 sh.PrintOut
9 End If
10 Next
11 Application.ScreenUpdating = True
12 End Sub

You shouldn't need to replace 'Do nothing as it is a comment, not code.

Regards,
Tom Ogilvy


"AlanN" wrote in message
...
Sorry ... one other thing. One sheet not to print is called sheet1, but the
other sheet not to print has the same name as the workbook name, how would I
change "source data" to the workbook name?

Thanks Again,

Alan
"Ron de Bruin" wrote in message
...
Try something like this

Sub test()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "source data" Or sh.Name = "Sheet1" Then
' Do Nothing
Else
sh.PrintOut
End If
Next
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"AlanN" wrote in message
...
I have a project where the resultant page tabs are variable in number and
the sheet names change everytime it's run.

Can someone suggest any code that will print all the pages in the active
workbook except the pages called "Sheet1" and "source data"?

Thanks, Alan N