Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Print Macro (EXCEL XP)

    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. [I am learning to code by reviewing the macro code generated by EXCEL from the recording of the keystokes].

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print Macro (EXCEL XP)

    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

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Print Macro (EXCEL XP)

    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.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Print Macro (EXCEL XP)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Print Macro (EXCEL XP)

    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

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Print Macro (EXCEL XP)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Print Macro (EXCEL XP)

    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[s]? Should I establish a custom toolbar and place custom macro buttons on that toolbar so that it is clear where the button[s] 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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Print Macro (EXCEL XP)

    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 <!post=Distributing an Excel application with toolbars (5/95/97/2000/2002),200526>Distributing an Excel application with toolbars (5/95/97/2000/2002)<!/post> 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.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Macro (EXCEL XP)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Print Macro (EXCEL XP)

    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.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Print Macro (EXCEL XP)

    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.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Print Macro (EXCEL XP)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •