Help with Print Dialog from Excel

KhanofTarkir

New Member
Joined
Sep 3, 2014
Messages
24
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
 

Some videos you may like

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
Joined
Mar 14, 2002
Messages
1,170
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
Joined
Jun 19, 2002
Messages
63,650
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
Joined
Sep 3, 2014
Messages
24

ADVERTISEMENT

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!
 

KhanofTarkir

New Member
Joined
Sep 3, 2014
Messages
24

ADVERTISEMENT

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
Joined
Jun 19, 2002
Messages
63,650
Do you have any hidden or protected sheets?
 

KhanofTarkir

New Member
Joined
Sep 3, 2014
Messages
24
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
Joined
Jun 19, 2002
Messages
63,650
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,470
Members
409,884
Latest member
Msinmath
Top