VBA: Printing selected worksheets using an Array

  • I'm able to print worksheets using the following VBA:

    Worksheets(Array("HOME", "Accounts", "Expenditure report", "Supporting schedules", "Payments")).Select

    I can generate the worksheets through using formulae and holding the string in cell B1, eg:

    "HOME","Accounts","Expenditure report","Supporting schedules","Payments"

    In the VBA I'm defining B1 as 'SheetsToPrint' and using that in the array rather than hardcoding it as above. Unfortunately, I can't get VBA which will compile. I've tried:

    I'm using Excel 365 for Mac

    Look forward to any suggestions


    Edited 2 times, last by Carim: Added Code Tags ().

  • You cannot convert a delimited string to an array simply by passing it to the Array function - you just end up with an array with one element that is the string you started with. You can use Split though:

    Worksheets(Split(SheetsToPrint, ",")).Printout

    for example, where B1 contains just:

    HOME,Accounts,Expenditure report,Supporting schedules,Payments

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!