Help with code for VBA print sheets

  • Hi,


    I am very new to VBA and have very little understanding of it.

    I have created a very simple Macro and i am struggling to find a way to improve it to my needs, although i believe it can be done.


    The below is linked to a button on a blank page in my workbook which allows me to print selected number of copies of each sheet.

    I create a copy of this workbook per customer i have but some customers don't need specific sheets and thus dont need them printed so i delete the unwanted sheet from the workbook for that particular customer but when i press the print button, the Macro will only print the first step. If i have deleted the second sheet my macro cant seem to pass onto the next sheet and so on. I am sure there is a way for VBA to skip a sheet if it is not available and go on to print the next available sheet. Any help on this would be greatly appreciated!


    Edited once, last by royUK: Add Code Tags, please use them in future when posting code to the Forum. Watch this to see how https://youtu.be/tlbM-IkMwRM ().

  • Hello and Welcome to the Forum :)


    Before getting specifically to your question ...


    Have you already defined the Print Ranges ... for your 24 worksheets ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Why an array, each array looks to be one sheet.


    I would actually have a Contents page and the number of copies in a column next to the sheet name. Then loop through each sheet in the index and print the required copies.

  • Hi and Thanks!


    Sorry but I may sound like a right novice here, what do you mean by defining the print ranges?

    The only thing i have done so far is make this macro, which works when all sheets are still in the workbook, as soon as i delete a sheet, the macro cant pass the first step.

  • Hi Roy,


    I actually have no reason why i have used an array, i decided to try and make this macro today and followed a video on youtube to try and set it up, not sure which is the best way to get the end result really, this macro just automates the printing process nicely, although stops working when i delete a page, i feel i just need a tiny bit more code to say something like


    If page missing - skip page and print next etc after each step.?

    Why an array, each array looks to be one sheet.


    I would actually have a Contents page and the number of copies in a column next to the sheet name. Then loop through each sheet in the index and print the required copies.

  • Try this


  • Hi and Thanks!


    Sorry but I may sound like a right novice here, what do you mean by defining the print ranges?

    The only thing i have done so far is make this macro, which works when all sheets are still in the workbook, as soon as i delete a sheet, the macro cant pass the first step.

    Hi,


    Turn on your macro recorder ... and go through the process of printing one range ...


    It will allow to understand some of the basics ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Morning Roy,


    Thanks, this worked perfectly! Didn't realise it would be such a simple fix. Exactly what i was after! :)

    Try this