We help IT Professionals succeed at work.

Excel 2010: Is there anyway to make a file default print the entire workbook, instead of just the active sheet?

WildSteer
WildSteer asked
on
I have an Excel 2010 file that contains two worksheets.  I need to print about 200 of these files. I need both of the worksheets to print though, not just the first active worksheet.  

I know I could manually select print 'entire workbook' in the print settings if I want to print one.  But I don't want to have to go into each file and tell it to print the entire workbook when I print all of these.

Is there a way to set this 'print entire workbook' as a default in the Excel program, itself? Or a registry hack?

Thank you very much!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
No default, no hack in the registry.  

However this is done simply by recording a hotkey macro in your personal workbook which will automatically open every time you open Excel.

Just create a macro.  Mine was activated by Ctrl Shift P but you can choose whatever you want.  

Then post this into the macro code via the vba editor

Sub PrintEntireWorkbook()
'
' PrintEntireWorkbook Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Sheets().Select
     ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Open in new window

CERTIFIED EXPERT

Commented:
I suppose I should note here that the personal workbook always opens hidden.  So if you ever need to edit anything on it, just unhide it and edit then rehide.

-SA

CERTIFIED EXPERT

Commented:
Option Explicit
Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Application.EnableEvents = False
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
Application.EnableEvents = True
Cancel = True
End Sub

Open in new window

CERTIFIED EXPERT

Commented:
It doesn't appear that there is a registry entry for this.

You could create a class module in the user's Personal.xls file (or in any hidden workbook stored in the user's XLStart folder)

You can find most of what you need here:
http://www.cpearson.com/excel/AppEvent.htm

Open the Visual Basic Editor in Excel by going to Tools -> Macro -> Visual Basic Editor. Locate VBAProject(Personal.xls) on the left side of the VB editor, click on it and then go to the Insert menu and choose Class Module and then paste the code below:

Option Explicit
Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Application.EnableEvents = False
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
Application.EnableEvents = True
Cancel = True
End Sub

Then double-click on the ThisWorkBook module of VBAProject(Personal.xls) and paste this code:

Option Explicit

Dim AppClass As New EventClass

Private Sub Workbook_Open()
Set AppClass.App = Application

End Sub

Click on the Save button in the VB editor. Then close and relaunch Excel.
Note: This will force the printing of all worksheets all the time, even if the user chooses to print only a selected area or activeworksheet

Author

Commented:
Thank you for your fast replies.

I like the 'personal.xls' option.

I'm following your steps.  At the end when I re-open Excel, MS VB opens with a "Microsoft Visual Basic for Application: Compile Error: User-defined type not defined" error.  It references (highlights) this portion when showing me the error:

AppClass As New EventClass (from the ThisWorkbook code)

I'm not sure what to do.

Author

Commented:
This is the error I'm getting.


compile-error.pdf
CERTIFIED EXPERT

Commented:
you need to add this code block to your personal book:
Option Explicit

Dim AppClass As New EventClass

Private Sub Workbook_Open()
Set AppClass.App = Application

End Sub

Open in new window

CERTIFIED EXPERT

Commented:
Oh I see that you did now that I've looked at the message.  Did you also add this code block as a class module in the VB editor?

Option Explicit
Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Application.EnableEvents = False
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
Application.EnableEvents = True
Cancel = True
End Sub

Open in new window

Author

Commented:
Yes, I did add that code block as a class module in the VB editor.

I'm going to attach a screen shot...works better.

Thank you!!!
screenshot.pdf
CERTIFIED EXPERT

Commented:
Ok sorry here is what you need to do: name the class module as Appclass

Add this line to the class module:

Public WithEvents App As Application

Open in new window


To do this
1)click on the class1 module
2)the properties hand between the box and the triangle & ruler in the bar across the top
3) type in Appclass
Appclass.png
CERTIFIED EXPERT

Commented:
Then change this on the workbook:

Option Explicit

Dim AC As New Appclass

Private Sub Workbook_Open()
Set AC.App = Application

End Sub

Open in new window

CERTIFIED EXPERT

Commented:
Let me know if you have any further problems

Author

Commented:
I made those changes.  I'll attach screen prints.  I do not get any VB errors when opening Excel 2010 now, but if I make a test workbook with three sheets, I go to File\Print and it still shows "Print Active Sheets".  It hasn't set it to 'Print Entire Workbook', yet. Am I missing something?
vb-screenshot.pdf
CERTIFIED EXPERT

Commented:
Are you checking the print preview? I think if you just press print it will print all three pages.  
CERTIFIED EXPERT

Commented:
It doesn't select the entire workbook to print until you press the print.  It doesn't matter what you select now it will always force the entire workbook to print.  I think you might need to have some cells used, but if you put 1 on A1 cell of sheet one, 2 on A1 of sheet 2, and 3 on A1 of sheet 3 and then just press print it should all roll off the presses for you.

Print Preview or other methods don't give an accurate representation of what is going to happen.

That said you might want to comment out the code if you don't want to to print the entire workbook all the time by just adding a ' in front of the items you added to the workbook.

Option Explicit

Dim AC As New Appclass
Private Sub Workbook_Open()
'Set AC.App = Application
End Sub

Open in new window

Author

Commented:
You're right, I was only checking Print Preview.  I never actually tried to print out 3 sheets of a workbook as a test.  So I just tried that now.  I selected my printer and hit print.  This is another VB error.  I didn't see this error, yesterday, because I never actually tried to print it.

Error:
Microsoft Visual Basic for Applications
Compile error:
Only comments may appear after End Sub, End Function, or End Property

When I click OK on that error and then try to close VB, the message 'This command will stop the debugger' pops up and I'll click OK.  Then the printer spits out only the first sheet of the three sheets i have in the workbook.  And yes, I labeled the first sheet with a 1 in cell A1, labeled second sheet with a 2 in A1, and labeled third sheet with a 3 in A1.  So it only printed the sheet with the 10 in it.

See attached.
newerror.pdf
CERTIFIED EXPERT

Commented:
Please delete the second end sub in your screenshot
It worked! Thank you for checking the code and seeing that second end sub.

It is printing all the worksheets in the workbook now.  

I might be posting a follow-up question regarding printing off 200 Excel workbook (yes, the ones with 1-3 worksheets each). I'd like to find out if I can just print them all off without having to open all of them?

Author

Commented:
Simply wonderful help from ScriptAddict!

Very knowledgeable, Very prompt, Very detailed (code and screenshots)-very helpful!

I have more questions and I hope I am lucky enough to get him again:)
CERTIFIED EXPERT

Commented:
Just select all the files and right click and select print.  :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.