+ Reply to Thread
Results 1 to 21 of 21

macro to print selected sheets

  1. #1
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    macro to print selected sheets

    hey, I need a help in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro to print selected sheets

    Hello HardWorker,

    Welcome to the Forum!

    Here is macro that will loop through all the worksheets in the workbook selecting only the ones you want. The macro allows you to skip sheets. Currently the only sheet skipped is "Sheet1". You add the names of the sheets you don't selected to the code. Place the name in double quotes and separate the names with a comma.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: macro to print selected sheets

    Hey thanks,
    What if I don't want to skip any sheets between my section. For example if I have 30 sheets in my excel file and I want to print consequently from sheet 1 to sheet 25.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro to print selected sheets

    Hello hard worker,

    This code will print sheets 1 to 25...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: macro to print selected sheets

    Both macro are not working. The one which suppose to skip "sheet 1" is not skipping that sheet and printing all sheets. And the one which suppose to print pages 1 to 25 is giving a error. It says "invalid next control variable reference".

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro to print selected sheets

    Hello hardworker,

    Here is the corrected code for the first 25 sheets. I forgot the index variable for the sheet name.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: macro to print selected sheets

    sorry to say but it is still not working.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro to print selected sheets

    Hello hardworker,

    This has not been my week for coding. Place this code in its own Module and mix any other code with it. The reason is it sets the beginning array index to 1, normally it is zero. This is necessary because the Sheets collection starts at one .
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: macro to print selected sheets

    Or you could go this way (See attached)
    You'll have to add the page number in the footer yet though.

    HTH
    John
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: macro to print selected sheets

    Code mention below is working when I am trying to print sheets from page 1 to XXX but if I am trying to start my print command from page 4. Then it is not working. Can you suggest why?and how I can fix that?

    Please Login or Register  to view this content.
    Last edited by shg; 03-23-2009 at 01:19 PM. Reason: code tags

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: macro to print selected sheets

    Please edit your post to add code tags.
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: macro to print selected sheets

    Any help on my last post.

  13. #13
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: macro to print selected sheets

    Hey I asked this question couple of days back but didn't get any reply. Can anybody help me.
    Thanks in advance....


    Code mention below is working when I am trying to print sheets from page 1 to XXX but if I am trying to start my print command from page 4. Then it is not working. Can you suggest why?and how I can fix that?

    Please Login or Register  to view this content.

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro to print selected sheets

    Hello hardworker,

    You need to change the starting index of the For Next from 1 to 4'
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    03-16-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: macro to print selected sheets

    Itried that but it din't work. Can you test yourself and let me know?

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro to print selected sheets

    Hello hardworker,

    Please be more specific about the problem. Are you getting an error? If so, what is the error number and description. Is it printing the wrong sheets? If so, which sheets are being printed?

  17. #17
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro to print selected sheets

    Hello Hardworker,

    I revised the macro and this works on my machine with no errors. The Sheets Array has to start at 1 (one). To build the array correctly a separate counter must be used. In the code "N" is the independent counter for the array. The names of the worksheets, starting with the fourth worksheet, are loaded into the array starting at position 1 (one).
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    08-30-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: macro to print selected sheets

    I want to use an excel macro to print only those sheets that are visible (There may be sheets which are hidden). When I use the code "Sheets(Array(sheet 1, sheet 2,...,sheet n).Select", it prints only these sheets and if any of these sheet is not visible it returns with error. Kindly help me.

  19. #19
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: macro to print selected sheets

    eahmed,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  20. #20
    Registered User
    Join Date
    08-30-2012
    Location
    Tampa
    MS-Off Ver
    MSOffice Pro Plus 2010
    Posts
    2

    Re: macro to print selected sheets

    How do I print only a grouped set of sheets (say -test1, test2, test3, test4, test5) but only the page that I select...there are 10 pages on each sheet. If I want page 1 of each or page 10 of each...

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: macro to print selected sheets

    KEMelton,

    As i said to eahmed above,

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1