+ Reply to Thread
Results 1 to 4 of 4

need macro that prints sheets listed in column a and the # of copies i want in column b

  1. #1
    Registered User
    Join Date
    12-08-2012
    Location
    Cape Girardeau, MO
    MS-Off Ver
    Excel 2003
    Posts
    5

    Exclamation need macro that prints sheets listed in column a and the # of copies i want in column b

    i have a workbook with 30 or so different sheets. the first sheet is a cover sheet offering the ability to print out x number of copies for specific sheets. in the cover sheet we have column "a" referencing all of the sheets available to print and in column b is the number of copies needed for the corresponding sheet. i need a macro that when prompted will print out the desired number of copies with the corresponding sheets. I would also like these referenced sheets to be hidden as to not confuse the user. also if a quantity of 0 is indicated, excel should not print that particular sheet.

    example

    "cover sheet tab"

    column a (a list of the optional sheets to print)

    sheet"name1"
    sheet"name2"
    ect.
    ect.
    ect.


    column b (desired number of copies)
    2
    1
    5
    ect.
    ect.


    Thank you for any help!

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: need macro that prints sheets listed in column a and the # of copies i want in column

    Hi ffxmp0,

    Welcome to the forum!!

    See how this goes:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    12-08-2012
    Location
    Cape Girardeau, MO
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: need macro that prints sheets listed in column a and the # of copies i want in column

    Thank you so much! Your code is so much better than what I made
    . Here was an example of the code I made

    Sheets("name of tab1").Visible = True
    Sheets("name of tab1").PrintOut Copies:=Sheets("cover page").Range("e3").Value
    'error trap
    On Error Resume Next
    Sheets("name of tab1").Visible = False
    Sheets("name of tab2").Visible = True
    Sheets("name of tab2").PrintOut Copies:=Sheets("cover page").Range("e4").Value
    'error trap
    On Error Resume Next
    Sheets("name of tab2").Visible = False
    Sheets("name of tab3").Visible = True
    Sheets("name of tab3").PrintOut Copies:=Sheets("cover page").Range("e5").Value
    'error trap
    On Error Resume Next
    Sheets("name of tab3").Visible = False

    ect. ect. for thirty plus tabs. I put the error trap in to compensate for the zero input in my code. it works, just not as clean and nice as your code. Thanks again!

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: need macro that prints sheets listed in column a and the # of copies i want in column

    Thanks for the feedback and you're welcome. Please remember to mark the thread as Solved when you get a moment.

+ 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