Printing from multiple worksheets (in Excel) 1

[galesjam]

Hi there!

I have an excel file that consists of around 20 worksheets, each having two pages on them. I want to print out the first page of all the worksheets. Is there an easy way to do this?

 

[MikefAtSfi]

I think so. First, highlight all your sheets. (click the first sheet tab, hold down the shift key, click the last sheet). Second, go to File then Print. The print dialog box will appear. Set your "Print Range" pages from 1 to 1.

Good Luck!

 

[galesjam]

I've tried highlighting all of the worksheets and then printing from page 1 to page 1, but I only get the first page of the first worksheet. Any other ideas?

 

[Hasit]

Take a look at this, it may help: faq68-1161

 

[DaleWatson123321]

Hasit, "thanks" for your plug of my FAQ. ;-)

galesjam,

The method described in the FAQ might be a potential solution.

However, here's another solution to consider...

The following VBA routine requires that you assign a range name to each Page1 range on each sheet.

Sub PrintAll_Page1()
Application.ScreenUpdating = False
Application.Goto Reference:="p1_sh1"
Printit
Application.Goto Reference:="p1_sh2"
Printit
Application.Goto Reference:="p1_sh3"
Printit
Application.ScreenUpdating = True
End Sub

Sub Printit()
Selection.Name = "PR"
ActiveSheet.PageSetup.PrintArea = "PR"
ActiveSheet.PrintOut Copies:=1
Application.Goto Reference:="R1C1"
End Sub

Creating Range Names is not difficult. While there are different methods of creating range names, the method I always recommend is the following:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

galesjam, If you would like me to email you the file I created, so you can see how the above VBA code works, please feel free to email me, and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[galesjam]

Dale,

Thanks for the code. It was extremely useful and did the trick nicely. It only took a few minutes to set it up and then I could print page 1 of all 100 worksheets with just a click of a button.

(Why oh why did Microsoft not have more printing options in Excel!)

You've definitely saved me loads of time, a very useful option!

Thanks.
- James