Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Vba Code For Printing/sheet Grouping Issue

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi all,

I have a simple peice of code for a button which when pressed selects specific sheets and then sends the user to the print dialogue box sohe or she can choose a printer etc and then print.

The issue I have is that once the user has printed the document then the sheets are still grouped together, which may cause some issues when they continue to use the spreadsheet.

Does anyone know how I might insert code to ungroup the cells once the document has exited the print dialogue box?

Thanks in advance for any help

View Answers     

Similar Excel Tutorials

Print All Worksheets at Once in Excel
How to print all worksheets at once from Excel. This saves you the time of having to go to each sheet individually ...
Group Data Together for Increased Readability in Excel
How to group data together or collapse it in order to focus only on the important data in Excel. This allows you t ...
Scale Data for Printing in Excel - Fit more onto a Printed Page
This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...
Print Comments in Excel
How to print the comments in a worksheet when you print from Excel. This includes how to print the comments within ...

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Print Specific Pages in Excel
- This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print 2 pag
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
Print The Current Worksheet in Excel
- This free Excel macro will print the current active worksheet in Excel. This means that whatever sheet you are currentl

Similar Topics

I have a Document with multiple sheets in it and I ask it to print entire document which would be 7 sheets (1 Page printed per sheet) but only the first five will print, then it comes up with a second print queue that I need to use and OK to print the balance of them with. I'm printing To a PDF creation program. it saves and Prints the first 5 pages then asks for another new name of the document to save the next few pages. my PDF Creator works fantastic with any other program. I just need to know how to tell it to print as many sheets as I have to print without it doing that. so I can print all sheets in a single PDF document.

I have a Document with multiple sheets in it and I ask it to print entire document which would be 7 sheets (1 Page printed per sheet) but only the first five will print, then it comes up with a second print queue that I need to use and OK to print the balance of them with. I'm printing To a PDF creation program. it saves and Prince the first 5 pages then asks for another new name of the document to save the next few pages. my PDF Creator works fantastic with any other program. I just need to know how to tell it to print as many sheets as I have to print without it doing that. so I can print all sheets in a single PDF document.

Perhaps someone can help with with what is probably a very simple problem. I've been trawling through the internet to find a solution and all I ever seem to find is a solution for the inverse of my problem.

So here it is...

I have a macro on a Word document that will print the document via the macro (bypassing the print dialogue box). What I WANT to do it to print the document to the user's DEFAULT printer (not their current printer). In the majority of cases the default printer will be the current printer, but there are times when a user has another word document open and has the printer set to another printer (let's call it printer 2). The default printer is printer 1 but because they already have a Word doc open and have the printer 2 selected, when the new document (with the macro for printing) is used, the document prints to printer 2 and not printer 1 (even though printer 1 is the default printer - it's just that at this point in time it's not the current printer).

Can anyone tell me what to put in the VBA script to make sure that when the document prints it prints to printer 1 (the default printer) every time, even if another Word doc is already open that has a different printer selected?

Your help, as always, is greatly appreciated.

Kind regards,


Have a excel document with multiple tabs that was created by an end user and
when the user prints the entire document to an HP Laserjet 4 printer it
prints fine.

When the end user sends the excel document to her manager and he prints the
document, the printer is asking him to load a A9 paper. It will not print
the spreadsheet until you load that paper.

The end user that created the document sends the document to severl other
people that have various printers. Some of them can print the document fine
while others can not.

Trying to figure out, why the document can print on some printers while
others it is asking your to insert a differnt page size other than letter.

Any help you can give me would be greatly appreciated.

Hi All:

I hope I can explain this correctly...

I was wondering if there is a code in VBA that instead of printing out a sheet it stores it and then prints at the end of a code

For Example: I am currently using something like this Code:

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Instead of printing the MiscRecon sheet when it get to this part of my code can VBA remember that I want this sheet printed and then print it AFTER the code has finished running?

Why I ask is that on my spreadsheet I run through a large code and the sheets are printed out as the code runs. I wanted to have the code run and then print the sheets at the end in a specific order. I know the best way was probably to write my code in the order I wanted the print outs but that isn't how I wrote it

The problem with printing the sheets as the code runs is that often others are using the printer and then the user has to sort through the printouts looking for their package. If the printouts came out at the end altogether the user can pick up the complete package from the printer without having to sort through other print jobs.

I HOPE I am making sense to someone


I am trying to print multiple worksheets in one print job. This is important because the document is being sent to a printer that staples an entire print job together.

After searching around a bit (google) it appears that there are quite a few others who have run into this same problem but the only solution I could find is for those who wanted to print to a pdf. This solution used code that sends instructions to pdfcreator.

Am I right in thinking that printing multiple sheets in one print job is impossible then?

Hi Guys

I have a process that goes through a few sheets of Excel and finds links in a particular column that go to web pages and these i then need to print to Adobe PDF

the links are similar to :

Which automatically send you to a print function

As side effect to this is that i then get multiples of the "PRINT" Dialogue boxes that remain open as the printing process to a default printer and save as filename is already automated in my vba code.

(screenshot attached of the dialogue box that needs to be removed)

Is there a way for me to be able to dismiss the Print Dialogues that pop up with a "Cancel" command so that the screen is tidy at the end of the process?

Thanks for any tips to help me achieve this



I have created a spreadsheet from which I want to print a report with set settings. What I would like is for the user to be able to select the printer they wish to print from (from the ones available to them - this could be printed anywhere around a large company) but then for the document to be printed with the settings that I choose for them. Is this possible? Here is my current code (note the use of "Application.Dialogs(xlDialogPrint).Show" to bring up the dialog box, but this will change the print settings I have defined).

Please Login or Register  to view this content.

Thanks in advance.

I have many files that consist of multiple sheets. When I sent it to print once, all my sheets print fine. When i send it to print twice or more, instead of printing in order it may print for example as follows

sheets 1,2,3, 1,2,3, 4,5, 4,5, 6,7,8, 6,7,8,

We then have to manually collate. It obviously is not that we have the collate turned off. I thought it was a printer driver issue and had people check it for me and our vendor came up with a solution that I forgot!!!! DUH I know.

It was a function in which you tell Excel that you want to 'combine' the worksheets into a one document. Not one sheet, just somehow it does not consider it a one document. It was deep under something that was not a print function. Cannot call the vendor again.

Morning all, hoping somebody could assist with a small query I have.

I have a workbook with a button that allows me to select worksheets that have been filled out and send them to the printer. At the moment it will print to whatever the default printer is set to. I use the below code to print the active worksheet;

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Is it possible I can specify what printer to use. I have tried using the 'To' command but this just does nothing.

I also need to be able to specify what sheets to print out. Is it possible to print a sheet by its name?

Thanks for any help in advance.


I am a continuing Computer support specialist and I am having a very rough time with excel and their print/print preview events. I am required to set up a User Form with a multi-select lstbox and two cmd buttons one for printing and one for exit printing. When a user clicks on the icon in the file menu for print or print preview the code for the BeforePrint event is fired. This action loads the lstbox with all sheets in the Workbook and then displays the User Form. The user can then select which sheets to print out or preview. One major problem I am having with it is the fact that the PrintOut method triggers the before print event. One more thing, when the user selects sheets and clicks print sheets cmd btn I am to Print the selected sheets and set their footers to match the sheetnames. Please help if you are able to. And Thank you very much in advance!!

Hiya Guys,
The program I'm working on will have a button that will allow you to print the sheet from within the visual basic application. I've tried:



But that doesn't open the print dialogue box, which is important, since the running environment for the finished product has multiple printers and so I want the user to be able to choose the one they want to print from.

Any help's appreciated.

I have a workbook with 20 sheets. Each sheet has been formatted with a print area to print a single page for that sheet. If I select entire workbook and view the job, there are, as expected, 20 pages. When the job is printed, I end up with 2 entries in my print 1 and the other 19 pages. BTW, the first sheet is very sparse (like a cover page).

If I delete the first sheet and print the entire workbook, it creates a single print queue entry for all 19 pages.

The reason that this is an issue is that when this (original) document is printed to a usb Lexmark printer, the sngle page job causes the printer to hang. It gets real messy from there, ultimately requiring a reboot.

If this same document is printed to a usb Canon printer, it still creates 2 print queue entries, but it all prints just fine.

Has anyone else experienced this problem and/or know what to do?

i just read the following code in the microsoft help

"The following example checks the PrintCount argument to determine the number of times the Print event has occurred. If it has occurred more than once, the Print event is canceled for the section."


Private Sub CustomerDetail_Print(Cancel As Integer, _
        PrintCount As Integer)
    If PrintCount > 1 Then Cancel = True
End Sub

That example is essentially what i want to do: limit the user to only 1 print out of a report, because they are printing controlled documentation and i don't want several copies just floating around.
Right now i have a command button triggering the print, and i am hiding that button after the print. But here is the question: what if the sheets don't print out (no ink, printer jammed, etc...)? They will need to hit the command button again, but it is gone!
Is there a way to determine that the printer printed your report successfully? Will the code above do that?



ok using the following code to bring up print dialogue

Application.Dialogs(xlDialogPrint).Show arg12:=2

but I have code which I only want to run if the print has been successful.
Obviously they can cancel out of this dialogue without printing, which I
want to know.

Is there any way in VBA to bring up the print dialogue box? I have an application which produces a report and then prints, but I need the user to be able to select the printer they use and the number of copies they require.

Hello All,

I have a problem which I and others here at my place of work cannot fathom out as to why it does not work. Why would a document not print out from the print dialogue box? Here is the code that I am using which does as it says, opens the document then opens the print dialogue box but will not print, that is when I hit print nothing happens.


Dim OpenWordDoc()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("G:\Network address here\My doc name.doc")

Many thanks for your help.


I have a strange Excel issue. It is almost hard to describe...

I have created a worksheet that I am giving out to others in the office. Things were going fine till I realized that people that printed to the same printer that I printed to had a problem that others did not have.

Our office uses Multi-function printers (MFPs). I have my printer properties set to automatically double side and "lock" print (don't print unless I enter my PIN by my user name on the machine). The problem is if I give the Excel file to someone else and they try and print the file to the same printer I use there is a problem. If they just hit print and to to that same machine the file is not there, but there is a print job on the MFP with may user name and it won't print unless I enter my PIN. However it is there data on the sheet that they printed.

What I don't understand is Word and a few other programs do not seem to carry over the Print Properties option like this so it looks to be Excel Specific thing

Note: User Name, PIN, double side options, staple, etc are all set at the print driver level (i.e. under Printer Properties. This can be set for all jobs (default setting) or adjusted per job when you go to print in the application.

This is in Excel 2003, with Ricoh 3045 printers using the PCL6 drivers, on a LAN.

Anyone have any ideas how to get Excel to not store the print properties options or remove them from a file so it would do what ever the user's defaults are set to? ... aka. like Word does ?

thanks for any ideas or help



I have a userform from which I need to be able to print a PDF document when the command button is pressed.

User enters the document number in docnum textbox (eg. 123)

On pressing the commandbutton printdoc, I need to be able to print document 123.PDF to default printer

All PDF Documents reside in a network folder mapped to the S:\PDF\drive.

If the document is not found I need to display an error message stating this and return focus back to the textbox.

If the document is found I need to display a message stating document sent to printer.

Is this possible?

Any help appreciated

Hey All,

Been trying to troubleshoot this issue for a user in my office. End user is using Excel 2003. She has a spreadsheet that has multiple sections and text formatted in color. When she chooses Print Preview, all the colored sections show up as Grey. When the document is printed, it does print correctly in color. All other applications are not affected by this.

I've tried changing printer settings, printer drivers, starting Excel in safe mode, starting Excel as if it's being run for the first time, and renamed the .XLB file to .XLB.OLD. None have corrected this. I haven't reinstalled Excel yet, but would prefer to avoid that step if possible.

Anything I can tell this user?

My question:

The issue:
I have a spreadsheet that contains worksheets that will NEVER print. However, users CAN add/copy additional worksheets that CAN print alongside standard printable worksheets...

So my issue is that while I can certainly modify the Workbook_BeforePrint(Cancel As Boolean) sub to edit previews/printing when a user actually CLICKS the print button, I cannot seem to stop the user from seeing (and therefore considering) an incorrect preview in the default file tab window.


Hi everyone!
Here is what I need: We have a set of documents, all pdf files, residing in a network server. The shipping personnel will have a Laserjet 8150 ND (also on the network) to print the document they need at the time of shipment, for a specific product (the pdf files are actually User's Manuals for various products we sell).

We would like to create an Excel form, listing those documents, with a selection method to indicate which document is to be printed and how many copies. Then clicking on a "PRINT" button will send a command to the printer to print the desired manual.

I searched the forum threads and noticed that the subject mostly deals with printing TO pdf, whereas my need is to print pdf FROM Excel, but no such a command exists.
Is that possible? and If so, any hints you can throw my way, please?

Thank you all.

I have a workbook that prints out a statement multiple times. This statement is just a template located on 1 tab.

VBA runs a loop that scrolls through a list of data which flows to the statements, changes the statement, then prints the statement, and then starts over with the new data and prints out the statement over and over depending on the range of data all on 1 tab.

So, as of right now, each time the loop prints out the statement, I get a separate print document sent to the printer for each loop. This works great until i have 3 or 4 people working on different workbooks that try to print at the same time. The statements are printed out at random and then must be separated (which could be hundreds of statements).

An answer to my problem is to be able to save the print out in an array or VBA and then send all the print outs to the printer at once as 1 document. That way, if 2 people printed out a workbook at the same time, the printer would print out 1 complete job and then print out the next...saving separating or random print out intertwined in specific sets of print outs.

Printing to .pdf is too slow and reduces the quality of the print image. I already tried that.

So, can I some how print out a tab, change it, and then print it out...but to only 1 print spool, all through in VBA?

Thanks for the help!!!!!!!!!

I am trying to write a macro that enables the user to:

Select a printer and then automatically print a hidden worksheet. The user will think s/he is printing the active sheet, but the hidden sheet is what should actually print out.

Here is the code I have thus far (I think I just need to add the prompt to select the printer):
Sheets("Print - Instructions").Visible = xlSheetVisible
Sheets("Print - Instructions").PrintOut
Sheets("Print - Instructions").Visible = xlSheetHidden

Note: the reason I have the worksheet that actually prints hidden is because if I print the active page, the pictures show up on top of the words in my text box (in case anyone was wondering).

I use Excel 2003. I need to create code that will print multiple sheets (Sheet1 - Sheet10) from a specific Xerox printer and from a specific tray (Tray 2). It has to be on secure print as well to where it will not print until you enter the assigned access code.

So, in summary I want the VBA code to doe the following for the print function: Go to File - Print Select this specific Xerox printer Select Tray 2 Select Secure Print Enter an access code (e.g. 013009) Enter the access code agian for the confirmation Click on OK to accept and exit out of the secure mode Click on OK to send the document to the printer
I tried just recording the macro but it would not include the parts where I selected Tray 2 or Secure Print with the access code.

Can someone help me determine the best way to code the print functions mentioned above?