CODEWITHSUNDEEP
arraysvbaexcelprinting
lostinOracle
lostinOracle

Reputation: 408

Excel vba - printing (array)

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

Upvotes: 0

Views: 2399

Answers (2)

user6432984
user6432984

Reputation:

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

Upvotes: 3

Comintern
Comintern

Reputation: 22195

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

Upvotes: 3

Related Questions