Print Macro (EXCEL XP)

Topic

New Reply

I have 23 separate EXCEL files–one for each principal. Each file contains three sheets–two with graphs that are printed to a Adobe pdf file and the third sheet contains the data elements for the graphs and is not printed. These work sheets are updated monthly and a new Adobe PDF file is created after the monthly update. I am very new to VBA. I had EXCEL create a macro from my keystrokes to print the two graph sheets [pages]. I select a print range, the print format [landscape], the printer and finally press ok to print. EXCEL has recorded my key stokes and the macro runs as intended. .

My question is that I would like to make this same print macro available in the 22 other files—but I don’t want to create something permanent in EXCEL that is always on the toolbar but only works with these 23 files. What is the most productive way to get the “recorded” print macro from my first file to other 22 files? Can you copy and paste macro code? I am afraid that the “recorded” macro references the current workbook and will not “run” if I merely paste it into the other 22 workbooks.
I was also hoping that I didn’t have to re-record the same keystokes for each of the other 22 files. THANKS.

Reply | Quote

Replies

Depending on the code and how explicit, it may work by just copying it.

Most of the macrorecorder code should deal with the active workbook, so the code could really run from anyworkbook.

Without seeing the code, I can be too definitive however…

Steve

Reply | Quote

Steve,
This is the code EXCEL created in Module1:

Sub PrintGraphMacro()
‘
‘ PrintGraphMacro Macro
‘ Macro recorded 2/25/2005 by Jim Clawson
‘
‘ Keyboard Shortcut: Ctrl+Shift+P
‘
Sheets(Array(“PAGE1”, “PAGE2”)).Select
Sheets(“PAGE1”).Activate
Range(“A1:K38”).Select
Application.ActivePrinter = “Adobe PDF on Ne05:”
Selection.PrintOut Copies:=1, ActivePrinter:=”Adobe PDF on Ne05:”, Collate _
:=True
End Sub

If this code is good, do I simply copy and paste it to Module 1 in each of the 22 other workbooks? THANKS.

Reply | Quote

Steve,
I spoke too soon…the recorded macro only “prints” the Page1 [name of first tab instead of sheet1] but doesn’t include Page2 [name of second tab instead of sheet2]….any ideas why the macro code doesn’t include the second page? I have even deleted the first recorded macro and re-recorded it with the same result—only page1 is in the pdf file. THANKS.

Reply | Quote

Would it be acceptable to set the print range for the two sheets in advance, outside the macro? If so, the following would print them:

Sub PrintGraphMacro()
Sheets(Array(“PAGE1”, “PAGE2″)).PrintOut ActivePrinter:=”Adobe PDF on Ne05:”, Collate:=True
End Sub

If you prefer to set the print range in the macro, you need two extra lines (you can’t set the print area for two worksheets at once):

Sub PrintGraphMacro()
Worksheets(“PAGE1”).PageSetup.PrintArea = “$A$1:$K$38”
Worksheets(“PAGE2”).PageSetup.PrintArea = “$A$1:$K$38”
Sheets(Array(“PAGE1”, “PAGE2″)).PrintOut ActivePrinter:=”Adobe PDF on Ne05:”, Collate:=True
End Sub

Reply | Quote

Hans,
Thanks..I have learned so much from reading all your Excel lounge posts and especially those that you answer for me. Sorry for my tardiness in “thanking” you as I had to step away from the lounge for several days to attend to an urgent family matter and was only able to try the code today. Again, thank you!!! Take care.

Reply | Quote

Hans,
One last question on this topic. I used your 2 page print macro and want to attach it to icon button. I have gone to Tools, Customize, Macros but now am stuck and can’t seem to find any ‘help’ in the help file. I just want a stand alone button, but it seems that I need to drag the ‘custom button” to an already existing toolbar. True? If so, how do you choose which toolbar to use? Does the toolbar selection impact the ability to display the button? In your development work, how do you decide where to place the custom button? Should I establish a custom toolbar and place custom macro buttons on that toolbar so that it is clear where the button is located versus being buried under the file, or some other toolbar menu? Also, when you “record” a macro using the EXCEL macro recorder, you are given an opportunity to assign a “hot key” combination to execute the macro. Can a hot key combination be assigned to macros that are created without the recorder? I copied your code from the lounge and pasted it in Module 1 of my EXCEL workbook. But I can’t seem to find the menu item in VBA to permit me to assign a hot key combination. I am sorry to be so dense and ask such detailed questions. THANKS.

Reply | Quote

1. You can create a new custom toolbar or use an existing menu or toolbar. A custom toolbar has the advantage that it doesn’t disturb the way the built-in toolbars look. As you found, you MUST create the custom toolbar before you can drag a button to it – sounds logical. See Distributing an Excel application with toolbars (5/95/97/2000/2002) for some handy tips.

2. Select Tools | Macro | Macros…, select a macro you recorded or wrote, then click Options… You can assign a keyboard shortcut there, or modify or delete an existing shortcut, and also enter/modify/delete the description for the macro.

Reply | Quote

Hans,
Thanks for the lightning quick response. I think I’ll read the referenced post before I do anything as I only want the custom button to display on these 23 workbooks and not become a “default” menu item everytime I launch EXCEL. Also, I assume that I’ll just copy the code to a module one in each of the 23 workbooks and assign the button for each of these workbooks. THANKS.

Reply | Quote

If you do not want the button and code to be available in all workbooks, you will have to store the custom toolbar and the code in each workbook where you need them. The Star Post I referred to explains how you can attach a toolbar to a specific workbook.

An alternative would be to put a command button directly on a worksheet (from the Forms toolbar or from the Control Toolbox).

Reply | Quote

Hi Jim,

Could I ask you a small favour?

Next time, could you please intersperse your text with some white space to separate paragraphs a bit?

It makes the messages much easier to read.

Reply | Quote

Yes, I agree. As I re-read my post, there are some clear paragraph line breaks that I ignored. Thank you for the suggestion. Take care.

Reply | Quote