Hi Everyone, We are trying to sele 1

[snorky]

Hi Everyone,
We are trying to select multiple sheets from an array of sheets which is generated on the fly ( as we dont always print the same sheets out ) how do I select multiple sheets at the same time ? the code below selects each one in turn, and we want to ADD the sheet to a multiple selectionof all the ones in the array and then print tham all out in 1 doc

For Each ws In array_of_sheets
ActiveWorkbook.Sheets(ws).Select
Next
Selection.PrintOut

Thanks
Snorky

 

[xlbo]

To select multiple sheets.....1st enter "Select Multipl Sheets" into VBA help. then select "Referring to more than 1 sheet" and it gives you:

Worksheets(Array("Sheet1", "Sheet2", "Sheet4&quot).Select


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[snorky]

We get an error doing this for some reason. Our "array_of_sheets" contains ( 1,3,4,5,12) these are the sheet nos we want printing but if we use

Worksheets(Array(array_of_sheets)).Select

we get an error ? ? ?

 

[SkipVought]

snorky,

You can't get around that each sheet in the array will print on it's own page(s), no different than your original approch with one exception...

If you are printing duplex, you would get sheet1 on page 1, sheet 2 on page 2 (back side of page 1) etc.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[snorky]

What we are trying to get is that all the sheets print on their own page but as one document. eg: the doc is sent to an intelligent photocopier and if we send each sheet seperately it treats each one as a seperate doc and takes ages , if we manually select all the sheets and print it sends them all together as 1 doc and prints a lot quicker.......we need to automate the selection ...HTH

Snorky

 

[xlbo]

To do it that way, you are gonna have to use this syntax:
Dim shtArr As Variant
shtArr = Array(1, 3, 4)
Sheets(Array(Sheets(shtArr(0)).Name, Sheets(shtArr(1)).Name, Sheets(shtArr(2)).Name)).Select

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[SkipVought]

snorky,

Don't forget to give Geoff a STAR for his expert post! It's just what you needed!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[snorky]

It would be if it worked. We get a invalid subscript error when we try this. But he can have a star anyway !

 

[xlbo]

Works for me - tested in XP
There's no reason it shouldn't work as sheets(shtArr(0)).name
will return the sheet name for the sheet number 1st in the array

Could this be to do with your Option Base setting - if it is set to 1 and you try and use shtArr(0) you will get a subscript out of range error

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes