RunPages and Print Page 1 of each worksheet

[alipaska]

Hey guys,

please help me. This should be really simple for you but I can't figure out why it doesn't run the pages and print each of them. This is the code:

Sub Macro()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True
End With
Next ws

End Sub

Thanks a lot.
Ali

 

[HalfAce]

Hello alipaska,
Let me guess. It's printing the active sheet the number of times as the number of sheets you have. (?)

Try selecting each sheet in your loop...

Sub Macro()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Select
    With ActiveSheet
        .PrintOut From:=1, To:=1, Copies:=1, Collate:=True
    End With
Next ws
End Sub

Hope it helps.

 

[brettvba]

Hi Ali,

Just modified your code slightly, you were refering to the Activewindow.selectedSheets which would only ever print the same page.

Sub Macro()
    Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            With ws
                 .PrintOut From:=1, To:=1, Copies:=1, Collate _
                 :=True
            End With
        Next ws
End Sub

HTH Brett

 

[alipaska]

Thank you so much. It works

I do have one more question though: if I want to do the same thing but only for specific worksheets in the workbook, how would I do that?

Thanks again for your help.

Ali

 

[HalfAce]

The best way to do that would depend on if there are (will be) only specific sheets you want to print, or specific sheets that you dont want to print.

In either case, if the sheet names will remain the same, can you provide their names?

 

[alipaska]

What if the sheets were called Sheet3-Sheet10, the ones I want to print. How would the code look like?

Thanks again,
Ali

 

[Norie]

Ali

Maybe something like this?

Sub Macro()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Select Case Mid(ws.Name, 5)
                Case 3 To 10
                    .PrintOut From:=1, To:=1, Copies:=1, Collate:=True
                Case Else
                            ' Do nothing
            End Select
        End With
    Next ws
End Sub

 

[alipaska]

Would that work for each tab named differently (for example : revenues, sales, profit, department1, department2 etc). Can it be applied generally, that it would always print sheets 3-10 (case 3 to 10)? Will I still be able to print last 7 tabs out of 10 with this code?

Thanks,
Ali

 

[erik.van.geit]

Hello, alipaska,

you can loop through the sheets using their indexnumbers
Sheets(i).Select
using Halfaces code

Sub Macro() 
Dim ws As Worksheet
Dim i As Integer
For i = 3 to 10
    Sheets(i).Select 
    With ActiveSheet 
        .PrintOut From:=1, To:=1, Copies:=1, Collate:=True 
    End With 
Next i
End Sub

kind regards,
Erik