Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Print all worksheets in workbook
Can someone give me macro to print all worksheets in a workbook? Thank you.
Dean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude worksheets from workbook print | Excel Discussion (Misc queries) | |||
Need to print a workbook but worksheets have diff print areas | Excel Discussion (Misc queries) | |||
How do I print a workbook in but only print selected worksheets? | Excel Discussion (Misc queries) | |||
Macro to open print window and set to print entire workbook | Excel Discussion (Misc queries) | |||
Need Help w/ Print Macro to Print All Visible Sheets (including Charts) in a Workbook | Excel Programming |