Help with Print Dialog from Excel

[KhanofTarkir]

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

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

 

[BobUmlas]

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]

Try

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]

Works perfectly - thanks BobUmlas and VoG!

 

[KhanofTarkir]

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]

The code that I posted works fine for me.

 

[KhanofTarkir]

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]

Do you have any hidden or protected sheets?

 

[KhanofTarkir]

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]

Try

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