Archive of Mr Excel Message Board
Back to Printing in Excel archive index
Back to archive home
Printing Multiple Excel worksheetsPosted by Bruce Moore on January 28, 2002 7:27 AM
Okay Maybe I asked this wrong. Let me try this again. I have 7 Named ranges on four different worksheets. I want to print those ranges to one PDF document, with each range on its own page, and I want this done from a macro. Possible??? I tried copying each of the ranges to one NEW worksheet, but the ranges have different column and row widths, and the copy function, even with Paste Special, does not retain column and row widths. I could create a new spreadsheet, by copying the existing one and removing the unneeded worksheets, but it still doesn't address printing multiple selections on multiple worksheets.
Re: Printing Multiple Excel worksheetsPosted by Ivan F Moala on January 28, 2002 7:51 AM
Try this code
Select sheets to print 1st then run code
Code is not mine
' prints the selected area on each of a set of selected worksheets on
' a single sheet
Dim oActive As Object
Dim oSheet As Object
Dim oSheets As Object
Dim wsPrint As Worksheet
Dim oLastPic As Object
Dim iPics As Integer
' remember where we are
Set oSheets = ActiveWindow.SelectedSheets
If oSheets.Count = 1 Then
Set oActive = ActiveSheet
Application.ScreenUpdating = False
oActive.Select ' otherwise we get lots of new sheets
Set wsPrint = Worksheets.Add
For Each oSheet In oSheets
If TypeName(oSheet) = "Worksheet" Then
iPics = iPics + 1
wsPrint.Cells(iPics * 3 - 2, 1).Value = oSheet.Name
wsPrint.Paste wsPrint.Cells(iPics * 3 - 1, 1)
wsPrint.Rows(iPics * 3 - 1).RowHeight = _
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.