# Help with Print Dialog from Excel

#### KhanofTarkir

##### New Member
Hi all,

I am having some trouble with the code below - when I run the macro I simply print out 7 duplicate copies of the target range of cells in the first tab as opposed to one copy of the target range of cells for each different individual tab. There are 7 tabs in total in the workbook, so I am guessing that the macro is looping the print dialog 7 times for a single tab. Any help would be greatly appreciated

Code:
``````Sub PrintQC()

Dim book As Workbook
Dim sheet As Worksheet
Dim text As String

Set book = ActiveWorkbook

For Each sheet In book.ActiveSheet

Range("A1:F23").Select
ActiveSheet.PageSetup.PrintArea = "A1:F23"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True

Next sheet

End

End Sub``````

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### BobUmlas

##### Well-known Member
Sub PrintQC()

Dim book As Workbook
Dim sheet As Worksheet
Dim text As String

Set book = ActiveWorkbook

For Each sheet In book.ActiveSheet
sheet.Activate '<=======================
ActiveSheet.PageSetup.PrintArea = "A1:F23"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True

Next sheet

End

End Sub

#### VoG

##### Legend
Try

Code:
``````Sub PrintQC()
Dim book As Workbook
Dim sht As Worksheet
Set book = ActiveWorkbook
For Each sht In book.Worksheets
sht.PageSetup.PrintArea = "A1:F23"
sht.PrintOut
Next sht
End Sub``````

#### KhanofTarkir

##### New Member
Works perfectly - thanks BobUmlas and VoG!

#### KhanofTarkir

##### New Member

Hi BobUmlas and VoG,

Actually, the code works fine but it seems that the printer is still only printing out 7 copies of the range of cells from the first sheet (active sheet). Could you maybe provide some insight into why this might be the case? Thanks!

#### VoG

##### Legend
The code that I posted works fine for me.

#### KhanofTarkir

##### New Member

Hi VoG, with your code it worked for me the first time, but now I receive an error message: Run time error 1004 Method 'Printout' of object '_Worksheet' failed. Do you know why this may be happening?

Thank you.

#### VoG

##### Legend
Do you have any hidden or protected sheets?

#### KhanofTarkir

##### New Member
Hi VoG, yes sorry I should have mentioned that - I do have 2 hidden sheets but I don't believe I have any protected sheets.

#### VoG

##### Legend
Try

Code:
``````Sub PrintQC()
Dim book As Workbook
Dim sht As Worksheet
Dim h As Boolean
Set book = ActiveWorkbook
For Each sht In book.Worksheets
h = sh.Visible
If Not h Then
sh.Visible = xlSheetVisible
End If
sht.PageSetup.PrintArea = "A1:F23"
sht.PrintOut
sh.Visible = h
Next sht
End Sub``````

Replies
0
Views
67
Replies
2
Views
48
Replies
0
Views
27
Replies
7
Views
62
Replies
1
Views
39