Filling an array with sheet names - VBA

[MojaveJim]

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:

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.

 

[Jeffrey Mahoney]

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]

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]

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