Macro to Print all worksheets in workbook
 

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

Macro to Print all worksheets in workbook
 

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
Dean wrote:
Can someone give me macro to print all worksheets in a workbook? Thank you.
Dean

Macro to Print all worksheets in workbook
 

Dean

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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

Macro to Print all worksheets in workbook
 

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

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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

Macro to Print all worksheets in workbook
 

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
DTHIS
www.nickhodge.co.uk


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

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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

Macro to Print all worksheets in workbook
 

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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


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

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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

Macro to Print all worksheets in workbook
 

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
DTHIS
www.nickhodge.co.uk


"Dean" wrote in message
...
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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


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

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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

Macro to Print all worksheets in workbook
 

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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


"Dean" wrote in message
...
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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


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

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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

Macro to Print all worksheets in workbook
 

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
DTHIS
www.nickhodge.co.uk


"Dean" wrote in message
...
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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


"Dean" wrote in message
...
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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


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

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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

Macro to Print all worksheets in workbook
 

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

"Nick Hodge" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


"Dean" wrote in message
...
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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


"Dean" wrote in message
...
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" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


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

Or a different reading of the question from Jason

Sub PrintAllWorksheets()
Worksheets.PrintOut
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


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