Excel vba - printing (array)

Question

quick question on why the below vba for printing won't work... If I have it set up individually (Sheet1.PrintOut) it prints out fine, but if I do it as array(Sheet1, Sheet2, Sheet3).PrintOut it doesn't work. Can anyone explain why?

Sub printnow()

Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim Sheet3 As Worksheet

With ThisWorkbook

Set Sheet1 = .Sheets("database1")
Set Sheet2 = .Sheets("database2")
Set Sheet3 = .Sheets("database3")

'Setting up the print setup
Sheet3.PageSetup.PaperSize = xlPaperLegal

Sheet3.PageSetup.Orientation = xlPortrait

'Print

End With

Array(Sheet1,Sheet2.Sheet3).PrintOut Copies:=1

End Sub

You can use ThisWorkbook.Sheets(Array("database1", "database2", "database3")).PrintOut Copies:=1 - that would be equivalent to Excel's "Print Active Sheets" option if you had selected multiple sheets. — YowE3K
– YowE3K, Commented Sep 8, 2016 at 2:33

Comintern · Accepted Answer · 2016-09-08 02:20:25Z

3

The Array function call returns a Variant() containing Sheet1, Sheet2, and Sheet3. A Variant() doesn't have a .PrintOut method - it isn't an object. If you want to call .PrintOut on each of the objects in the array, just loop over it:

Dim sheet As Variant
For Each sheet In Array(Sheet1, Sheet2, Sheet3)
    sheet.PrintOut Copies:=1
Next

edited Sep 8, 2016 at 2:20

answered Sep 8, 2016 at 2:15

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 3 · Accepted Answer · 2016-09-09 01:18:56Z

3

You can pass an array of sheet names to Sheets and it will process them.

Sheets(Array("database1", "database2", "database3")).Printout Copies:=1

You can also use the Sheet.Name:

Sheets(Array(Sheet1.Name, Sheet2.Name, Sheet3.Name)).Printout Copies:=1

Or you can use the sheet's indices:

Sheets(Array(1,2,3)).Printout Copies:=1

edited Sep 9, 2016 at 1:18

answered Sep 8, 2016 at 2:45

user6432984

4 Comments

lostinOracle Over a year ago

but would it keep the page orientation/cell selection specifications that I chose?

user6432984 Over a year ago

You have the same optional printout parameters. If you don't specify any parameters than each sheet will use the settings that were used when it was last printed

lostinOracle Over a year ago

lets say I renamed the above Sheet1 as Page1, Sheet2 as Page2, and Sheet3 as Page3, with all else equal. what can I do to make Sheets(Array(Page1, Page2, Page3)).Printout work? Or is a loop the only way

user6432984 Over a year ago

I updates my answer to demonstrate the other options.

Collectives™ on Stack Overflow

Excel vba - printing (array)

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related