VBA: Worksheet Array

[AngAgius]

I'm using Array("Sheet1", "Sheet2", "Sheet3") to select a number of worksheets to copy to a new file to Print or create a PDF. Code works fine.

However, I would like the user to select which sheets to select for copy & print, this means the Array will vary in name & number selected. How can I do this? I've searched & searched but can't find it.

I'd appreciate if you could help me out, it's driving me nuts

 

[VoG]

For example

ActiveWindow.SelectedSheets.PrintOut

 

[AngAgius]

Thanks VoG but that's not really my problem

How do I change Array("Sheet1", "Sheet2", "Sheet3") based on user input so for example if there is an additional worksheet the user requires so Array("Sheet1", "Sheet2", "Sheet3","Sheet4")..... n.b the sheets could be named as anything

The code copies the sheets to a new workbook, then range values, can either print or create PDF, saves the new workbook

 

[VoG]

So how are the users inputting the sheets that they want to operate on?

 

[AngAgius]

as a list on a Parameters tab

 

[VoG]

as a list on a Parameters tab

I don't know what that means.

 

[AngAgius]

Sorry VoG, I should have been more clear.

There is a worksheet for Parameters.... it would be something like:

Input Worksheets to copy:
Sheet1
Sheet2
Sheet3
ect

 

[VoG]

Maybe like this

With Worksheets("Parameters")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    mysheets = Application.Transpose(.Range("A1:A" & LR))
    Sheets(mysheets).Copy
End With

 

[AngAgius]

Thanks VoG, I'll give it ago