Macro to Print all worksheets in workbook

[Dean]

Can someone give me macro to print all worksheets in a workbook? Thank you.
Dean

 

[Jason Lepack]

Sub AllWorkSheets()
' Coded on 12/10/2006 at 12:45 AM
' Coded by Jason Lepack
'
' Loops through all worksheets and lists their names in a new worksheet
'
Dim wsNew As Worksheet, wsTemp As Worksheet
Dim r As Range
Set wsNew = Sheets.Add ' add a new worksheet
wsNew.Name = "All_Sheets" ' named "All_Sheets"
Set r = wsNew.Range("A1") ' cell to place the name in
For Each ws In ActiveWorkbook.Sheets 'loop through all worksheets
r = ws.Name ' put the name of the worksheet in the new sheet
Set r = r.Offset(1, 0) ' move down one cell
Next ws
' clean up
Set ws = Nothing
Set r = Nothing
Set wsNew = Nothing
End Sub

Cheers,
Jason Lepack

 

[Nick Hodge]

Dean

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Dean]

Yes, I like that, too.

How do I change it to unprotect all sheets?

Also to protect all sheets with:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

Thanks guys!
Dean

 

[Nick Hodge]

Dean

This will unprotect a sheet at a time, print it and re-protect, you can add
more parameters if you need but many in the example you gave are defaults
and therefore don't need to be explicitly set

Sub PrintAllWorksheets()
Dim wks As Worksheet
For Each wks In Worksheets
wks.Unprotect Password:="WhatEver"
wks.PrintOut
wks.Protect Password:="Whatever"
Next wks
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Dean]

The commands I gave you are ones that I am used to using after
"worksheetname.select". I'm not sure how to integrate:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

with your macro, which uses "worksheetname.protect". Kindly clarify.

Actually, though it does appear that I was wanting to sandwich the print
routine between unprotect and (re) protect, I wasn't. In fact, I can't
imagine that one would need to unprotect just to print. I just liked your
way of getting all the sheets which is why I asked how to modify what you
gave me. The way I've been doing it, I have to select each by name and that
makes me look like a rookie and, if I rename a sheet or add a sheet, I have
to edit the macro!

Thanks!
Dean

 

[Nick Hodge]

Dean

The worksheets are known within Excel as a collection (A collection of
worksheet objects). A very good way or iterating through each element in a
collection is a for each...next loop. All my code does is declares a
variable (wks, but could be called anything valid), which holds a worksheet
object each time it loops through the collection, so no matter how many you
add or take away, each one gets looked at. This then means that wks is
actually the 'current' worksheet object you are looking at, so to use your
protect code you would just use...

wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

This saves all the selecting, activating, etc which slows code down. My
point about defaults still stands. The above is actually setting everything
to True which is a default so the cod above is equivalent to

wks.Protect

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Dean]

Thanks for the detailed tutorial. I am confused about the default issue,
though. I just recorded a macro where I did worksheet protect only; and
only the first two "=True" conditions were recorded. Then, I checked off
the next three boxes, which is the way I like it, and recorded doing that
and got what I've been sending you. I think my "default" is the first two
true boxes checked and everything else, unchecked/untrue. Do you understand
what I am saying? For this worksheet, I want all of the first five boxes
checked. Perhaps your default is all of those first 5 boxes, but no others,
checked - though that seems like a heck of a coincidence.

D

 

[Nick Hodge]

Dean

I didn't actually check...It was more the point as your code was obviously
recorded that the recorder quite often throws out 'inefficient defaults' I
believe your explanation looks valid and sorry if I confused you

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Dean]

No problem - thanks. You were very helpful, otherwise!