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


Free Excel Forum

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 Video Tutorials

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 am trying to get a print dialogue to appear as part of my code, not just to
print something. In other words, I have code that needs to run, and before
it does, I want to make sure the person has a chance to print. That said, if
they choose NOT to print, by selecting cancel, I need the code to continue
from there. Ideally I'd like the following sequence to occur upon initiation
of the macro:

1) quick informational dialogue that says "Warning: Once you run this, you
will clear the information on the form. Do you want to continue (you will
have a chance to first print)?"
2) if they select no, the whole code stops
3) if they select yes, a print dialogue comes up, giving them the normal
options they'd have under ctrl-p
4) if they select to print, it prints, and the code runs (basically takes
the data and puts it on a separate, holding sheet
5) if they select to cancel print, the code still runs and cleans the sheet
to the holding place

Right now, I have it successfully doing the transfer and clearing of data.
I need that initial piece of asking the questions, and giving the print
dialogue option.

Thanks for any help. My recording macro function will only show how to
print, not the dialogue option.

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


We are having a serious issue printing from an excel document. Each time we try to print from the print icon or print preview menu, the cells and row information shifts. The text and the shapes would shift off to the right. The spreadsheet will print out ok, but the docment itself will be left offset. We've tried reinstalling the printer driver,printing from a different printer,running all of the ms office updates, and printing frome different pc's. The only thing in common is that each pc is on the excel 2003 version. I've tried printing from the 2007 version of excel and had no problem. Could this be a version issue or wait. If anyone out there could help us out with this, your expertise would be greatly appreciated.


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?


I am in need of some code assistance.

I would like to add a print button that once pressed it will ask the user to choose what sheet they want to print or choose to print all sheets.

the sheets will all print the same range A25:Q35 either individually or all the sheets.

Is this possible?

HI Guys,

I have a spreadsheet which is used to print out a form on three pages. I have been using the code below to print the sheets off with no problems. The user has a dialog box and can "tick" to say which of the three sheets they want printed. Until a couple of weeks ago if I printed to adobe acrobat I would get a multiply page document without any issues. Now for some reason I get asked for a name for each page as it's printed and it is treated as a seperate document. I haven't got a clue what's changed uness Excel has been updated, or acrobat has and something has been changed. Any clues on how to get round this would be appreciated. THe code I have been using is:

Sub printpreview()

x = 0
Rem show a print preview to allow for corrections.

If Range("j38").Value = True Then
x = 1
End If
If Range("j39").Value = True Then
x = x + 3
End If
If Range("j40").Value = True Then
x = x + 7
End If

If x = 1 Then
End If
If x = 4 Then
Rem select first two sheets for printing.
Sheets(Array("Sector1", "Sector2")).Select
End If
If x = 11 Then
Sheets(Array("sector1", "Sector2", "Sector3")).Select
End If
If x = 3 Then
End If
If x = 7 Then
End If
If x = 10 Then
Sheets(Array("Sector2", "Sector3")).Select
End If
End Sub

I then use the print button from the print preview. Just to add to my confusioin if I print to my printer it works fine. I've tried with pages collated and not and same problem.

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.

I wrote (with lots of help!) a bit of code to have a header print out on page one of a worksheet, but not on the rest of the pages. The only way I knew to do this was to use the BeforePrint function. But now (due to the nature of BeforePrint) when the user chooses File > Print, the print menu only shows up after the printer has received the document. I would like the user to be able to use the print menu to choose the printer BEFORE the printer receives the document. Is there any way to do this, or am I only dreaming?

Edited to correct grammar

We are having a serious issue printing from an excel document. Each time we try to print from the print icon or print preview menu, the cells and row information shifts. The text and the shapes would shift off to the right. The spreadsheet will print out ok, but the docment itself will be left offset. We've tried reinstalling the printer driver,printing from a different printer,running all of the ms office updates, and printing frome different pc's. The only thing in common is that each pc is on the excel 2003 version. I've tried printing from the 2007 version of excel and had no problem. Could this be a version issue or wait. If anyone out there could help us out with this, your expertise would be greatly appreciated.


When I select print on an Excel (.xls) document in Explorer, or right-click
Print an excel icon in Exchange, only the first sheet is printed. Is there a
default I can change so that these actions print ALL (non-blank) sheets?

(I know that I can open the document and Print all sheets from the Print
dialog. I want to accomplish this printing without explicitly opening the


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!!

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?

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 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.


Please help...

I have created a very simple macro to print out a whole document. However, rather than just printing out if someone pressed the button by accident, I would like to create a pop up box saying something like:

"Are you sure you would like to print the Whole document?"

Options to select:

"Yes, Print entire Document"

Please advise...

Many Thanks


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.


I need to print a number of sheets in a VB document on the click of a command button but I don't want it to print each sheet seperatly. I also want it to ask which printer to print it from?

Any help wouldbe appreciated.



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?

I have a EXCEL form that I store on a public drive that everyone can access using a shortcut placed on their computer Users open the document from their computer put in their data and are supposed to submit it to MY printer. MY printer can be accessed on that drive. My problem is I made a MACRO button so all they have to do is click on the form and it will print, clear the form and exit it without saving it. BUT it isnt printing on MY printer it is printing on their default printer. I have tried every thing to change it so it will print on mine it wont unless the user clicks on the down arrow and manually selects my printer Please help. thanks