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

 

[KhanofTarkir]

Hi VoG,

Thank you - that code now works and I can print out cell ranges for individual sheets in my workbook.

It seems that the cells in hidden worksheets are also being printed out - considering that the first and last cells of every excel workbook I have to deal with are hidden, would it be possible to incorporate coding to prevent printing out the first worksheet and last worksheet (both hidden)? Thanks!

 

[VoG]

Try

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

 

[KhanofTarkir]

Hi VoG,

The code works great! One last thing - would it be possible to have code that tells excel that under Print Preview under 'Scaling' I want it to Fit to: 1 page(s) wide by 1 tall for all printouts?

I believe this dialog box pops up if you go to the upper left hand corner window button in excel and go to print --> print preview --> page setup. Thanks!

 

[VoG]

Try

Sub PrintQC()
Dim book As Workbook
Dim sht As Worksheet
Set book = ActiveWorkbook
For Each sht In book.Worksheets
    If sht.Visible = xlSheetVisible Then
        With sht.PageSetup
            .PrintArea = "A1:F23"
            .FitToPagesTall = 1
            .FitToPagesWide = 1
            .Zoom = False
        End With
        sht.PrintOut
    End If
Next sht
End Sub