Filling an array with sheet names - VBA

MojaveJim

New Member
Joined
Jun 7, 2019
Messages
5
Greetings,

I need to print a set of user-defined sheets form a workbook and I want them in ONE print job, not individual print jobs. Reason being, I want to create a PDF file with 1-10 pages, not ten pdf files.

I can gather the sheet names the user chooses and put them in a place in the spreadsheet in any fashion required by VBA, that isn't the problem.

The problem is, I don't know how to make a dynamic array, I can't figure out how to tell VBA to use those three (or 7 or 10) sheet names. I can list them in a column, I can put them altogether with quotes and commas, I just can't get VBA to see them.

This accomplishes what I want, except it isn't dynamic:

VBA Code:
ThisWorkbook.Sheets(Array("Factory Control","Packaging","Packing list")).PrintOut

Like I said, I can arrange the sheet names in any fashion required, but any kind of DIM statement or SET or "=" statement I write doesn't agree with VBA and I can't populate that array section.

Please help. This doesn't seem that hard yet I'm stuck. I've never done arrays before.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
I took some old code and simplified it. This reads a list from a sheet and opens print preview to print those given sheet names.

Sub PreviewSheets()
Dim WS As Worksheet
Dim WSNames() As String
Dim X As Long
Dim Sht As Worksheet

Dim R As Range
Dim Cel As Range

ReDim WSNames(100)

X = -1
For Each Cel In Selection
X = X + 1
WSNames(X) = Cel.Value
Next Cel

ReDim Preserve WSNames(X)
'Sheets(WSNames()).Select
Sheets(WSNames()).PrintPreview


End Sub
 

MojaveJim

New Member
Joined
Jun 7, 2019
Messages
5
Jeffrey,

You are a beautiful human being and a credit to the planet!

I don't understand this, but it works. I'll try and break it down and see if I can make sense of it.

For anyone else playing along, the names of the different sheets are in contiguous cells in a column, WITHOUT double quotes, even those names with a space. The cursor must highlight the cells that you want to be included in the print job. That's easily solved with VBA.

Thank you Jeffrey! If you're ever in Los Angeles Orange County I will buy you a beer or three!
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
You can change that so that it reads the list of sheet names from the same range. Just create a named range and use:
Sheets("Setup").range("YourNamedRange") instead of selection

When you feed the print preview the list of sheet names in the array, it cannot contain blanks. That's the reason for setting the WSNames() to a large value at first and then REDIM PRESERVE shortens the array to only the used values.

I'm glad it worked for you
 

Watch MrExcel Video

Forum statistics

Threads
1,127,651
Messages
5,626,089
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top