RunPages and Print Page 1 of each worksheet

alipaska

New Member
Joined
May 13, 2005
Messages
38
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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...
Code:
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
Hi Ali,

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

Code:
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

New Member
Joined
May 13, 2005
Messages
38
Thank you so much. It works :biggrin:

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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

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

New Member
Joined
May 13, 2005
Messages
38
What if the sheets were called Sheet3-Sheet10, the ones I want to print. How would the code look like?

Thanks again,
Ali
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ali

Maybe something like this?
Code:
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

New Member
Joined
May 13, 2005
Messages
38
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, alipaska,

you can loop through the sheets using their indexnumbers
Sheets(i).Select
using Halfaces code
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,678
Messages
5,573,608
Members
412,539
Latest member
itchy00
Top