CODEWITHSUNDEEP
excelvbaprinting
nosirrah
nosirrah

Reputation: 31

how to print specific sheets in VBA?

I have sheets A B C that i want to go ahead and print out. However, I can only print out one of the sheets currently i was wondering if you know what might be going wrong with it ?

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "a" Then
sh.PrintOut Preview:=False, ActivePrinter:="----", PrintToFile:=True, PrToFileName:=PSFileName
End If
Next sh

I was wondering how i might be able to add sheets B and C onto it ? I thought all i had to do was something like:

sh.name : array("A", "B" ... ) but that made me run into an error. Was wondering if anybody has similar issues in the past?

Thanks !

Upvotes: 0

Views: 3953

Answers (3)

Tim Williams
Tim Williams

Reputation: 166126

If you want to print all 3 in one print job:

ThisWorkbook.Worksheets(Array("SheetA", "SheetB", "SheetC")).PrintOut copies:=1, _
                                           collate:=True, IgnorePrintAreas:=False

Upvotes: 2

braX
braX

Reputation: 11755

You can use a Select Case for this.

For Each sh In ThisWorkbook.Worksheets
  Select Case UCase$(sh.Name) 
    Case "A", "B", "C"

      ' print code goes here

  End Select
Next

Upvotes: 0

Toddleson
Toddleson

Reputation: 4457

What you're looking for is:

    If sh.Name = "A" or sh.Name = "B" or sh.Name = "C" Then

Here's how your code looks with that implemented:

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "A" or sh.Name = "B" or sh.Name = "C" Then
sh.PrintOut Preview:=False, ActivePrinter:="----", PrintToFile:=True, PrToFileName:=PSFileName
End If
Next sh

If you wanted an array you could use:

Dim SheetList() as Variant
SheetList = Array( Sheets("A"), Sheets("B"), Sheets("C"))

Which you would implement like:

Dim sh As Variant
Dim SheetList() As Variant
SheetList = Array(Sheets("A"), Sheets("B"), Sheets("C"))
For Each sh In SheetList
sh.PrintOut Preview:=False, ActivePrinter:="----", PrintToFile:=True, PrToFileName:=PSFileName
Next sh

Upvotes: 0

Related Questions