[VBA Code] Printing multiple sheets

E

elmerg

New Member
Joined
Jun 24, 2008
Messages
21
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hey everyone. I have a problem. I've searched the forum, and couldn't find an answer to my question, so I figured I'd go ahead and post it.

I need to print multiple sheets from a single workbook (that have appropriate print areas already set up). I want to assign this macro to a button, so that I can just click one button and get a copy of every sheet.

Each of the sheet names is unique, and stored in column A on a sheet titled LIST, since there are four sheets of stuff that don't need to print. Each of the uniquely-named sheets are hidden as well. What I need:

* Print all sheets, using the names on LIST in column A (A1 to the last one with data; the names are alphabetized from ADAM on down to WILLOW).
* Ask for confirmation 'Are you sure you want to print all saved sheets?'
** If yes, print all the sheets.
** If cancel, do nothing.

Any help would be appreciated. I can see how Id do everything but using the A column to define what names to print, I think.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Try

Code: 
Sub PrtAll()
Dim response As VbMsgBoxResult, ShName As String, LR As Long, i As Long
response = MsgBox("Do you want to print all sheets", vbQuestion + vbYesNo)
If response = vbNo Then Exit Sub
LR = Sheets("LIST").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    ShName = Sheets("LIST").Range("A" & i).Value
    Sheets(ShName).PrintOut
Next i
End Sub
 
Upvote 0
Hmm. I'm not sure how much that worked. I assigned the macro fine to the button, but I get a 400 error with just an OK or HELP button in it. Is the macro accounting for the fact that LIST and all of the sheets in LIST are hidden?
 
Upvote 0
Ah, I missed that they were hidden. Try

Code: 
Sub PrtAll()
Dim response As VbMsgBoxResult, ShName As String, LR As Long, i As Long
response = MsgBox("Do you want to print all sheets", vbQuestion + vbYesNo)
If response = vbNo Then Exit Sub
LR = Sheets("LIST").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    ShName = Sheets("LIST").Range("A" & i).Value
    With Sheets(ShName)
        .Visible = True
        .PrintOut
        .Visible = False
    End With
Next i
End Sub
 
Upvote 0
Woot. That worked, I believe. Thanks for the help!
 
Upvote 0
VoG II said:
Ah, I missed that they were hidden. Try

Rich (BB code): 
Sub PrtAll()
Dim response As VbMsgBoxResult, ShName As String, LR As Long, i As Long, 
response = MsgBox("Do you want to print all sheets", vbQuestion + vbYesNo)
If response = vbNo Then Exit Sub
LR = Sheets("LIST").Range("A" & Rows.Count).End(xlUp).Row
Application.Screenupdating = false 
For i = 1 To LR
    ShName = Sheets("LIST").Range("A" & i).Value
    With Sheets(ShName)
        If .visible = true then
            .printout
        else
            .Visible = True
            .PrintOut
            .Visible = False
        end if
    End With
Next i
Application.Screenupdating = true
End Sub
Click to expand...


Just a slight improvement to VOG's code, Stop Screenupdating to speed up the code and the if ... then to make sure that only sheets that were hidden in the first place are hidden after the code has run.
 
Upvote 0
Hi,

I tried using that one and got "Run Time Error 1004".

Is this a macro that works its way through your list of worksheets until it reaches the end and prints each sheets out individually? (I have been searching the forums to see if there is an existing example)

Also, will that print the worksheets as "1 document". The reason I ask this is that I have played with the select all>print for a worksheet that I have that has 20+ worksheets and I have my print settings as double sided and staple as a default. I would prefer to print out the work sheet and staple (as per default print setting) then move on to the next worksheet etc.


Michelle
 
Upvote 0
When the code errors click the Debug button - which line is highlighted?

The code actually prints only those sheets listed in column A of sheet LIST but it could easily be modified to print every sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Code to print sheets between 2 particular sheets
2
Replies
15
Views
227
jolivanes
jolivanes
I
  • Solved
Print sheets based on checkbox
Replies
3
Views
172
John_w
J
E
  • Question
VBA for printing multiple tabs excel for mac
Replies
5
Views
118
EBahn
E
T
  • Solved
Print Sheets Using Cell Values
2
Replies
15
Views
385
Micron
M
O
  • Question
Printing multiple times with changing data
Replies
1
Views
226
orzys
O

Forum statistics

Threads
1,214,922
Messages
6,122,281
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top