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!

Sub PrintSpecificSheets()


ThisWorkbook.Worksheets(Array("NEW VEHICLE PROFILE")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("FRONT COVER SAMPLE PORTRAIT")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("FRONT COVER SAMPLE LANDSCAPE")).PrintOut copies:=10
ThisWorkbook.Worksheets(Array("VEHICLE FILE CONTENTS")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F39 - ELE LOOM BUILD S6400")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F40 - ELE LOOM BUILD S84+S94")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F1 - VEHICLE SPEC")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F9 - PDI CHECK LIST")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8.4F1 - APPROVAL NO. CHECKLIS")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP10F1 - RECORD OF NON.CONFORMI")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP9F5 - VEHICLE HANDOVER CHECK")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F7 - PRODUCTION CONTROL PLAN")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP5F1 - CUSTOMER SATISFACTION")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F33 - ENGINE COLLECTION-DELI")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F35 - ENGINE BAY FLUID CHECK")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F42 - SWEEPER MODULES")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F44 - FINAL M. INSPECTION")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F47 - CHASSIS TANK MOD CHEC ")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("6400 WVTA - EURO6")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F10 - PERIODIC COP AUDIT")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F16 - SKID PDI CHECK LIST")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F37 - FINISHED SKID CHECKLI")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("WHEEL NUT LETTERHEAD")).PrintOut copies:=1
ThisWorkbook.Worksheets(Array("QP8F11 - PRE-INSPECTION IVA")).PrintOut copies:=1
End Sub

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

Quote from royUK

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.

Morning Roy,

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

Quote from royUK

Try this

Code

Sub PrintSpecificSheets()
On Error Resume Next
ThisWorkbook.Worksheets("NEW VEHICLE PROFILE").PrintOut copies:=1
ThisWorkbook.Worksheets("FRONT COVER SAMPLE PORTRAIT").PrintOut copies:=1
ThisWorkbook.Worksheets("FRONT COVER SAMPLE LANDSCAPE").PrintOut copies:=10
ThisWorkbook.Worksheets("VEHICLE FILE CONTENTS").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F39 - ELE LOOM BUILD S6400").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F40 - ELE LOOM BUILD S84+S94").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F1 - VEHICLE SPEC").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F9 - PDI CHECK LIST").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8.4F1 - APPROVAL NO. CHECKLIS").PrintOut copies:=1
ThisWorkbook.Worksheets("QP10F1 - RECORD OF NON.CONFORMI").PrintOut copies:=1
ThisWorkbook.Worksheets("QP9F5 - VEHICLE HANDOVER CHECK").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F7 - PRODUCTION CONTROL PLAN").PrintOut copies:=1
ThisWorkbook.Worksheets("QP5F1 - CUSTOMER SATISFACTION").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F33 - ENGINE COLLECTION-DELI").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F35 - ENGINE BAY FLUID CHECK").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F42 - SWEEPER MODULES").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F44 - FINAL M. INSPECTION").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F47 - CHASSIS TANK MOD CHEC ").PrintOut copies:=1
ThisWorkbook.Worksheets("6400 WVTA - EURO6").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F10 - PERIODIC COP AUDIT").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F16 - SKID PDI CHECK LIST").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F37 - FINISHED SKID CHECKLI").PrintOut copies:=1
ThisWorkbook.Worksheets("WHEEL NUT LETTERHEAD").PrintOut copies:=1
ThisWorkbook.Worksheets("QP8F11 - PRE-INSPECTION IVA").PrintOut copies:=1
On Error GoTo 0

Display More