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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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,456

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,344
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,022
Messages
5,834,994
Members
430,330
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top