Archive of Mr Excel Message Board


Back to Printing in Excel archive index
Back to archive home

Printing Multiple Excel worksheets

Posted 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 worksheets

Posted 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


Sub MultiSheetPrint()
' 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
Selection.PrintOut preview:=True
Exit Sub
End If
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
oSheet.Activate
Selection.CopyPicture
wsPrint.Cells(iPics * 3 - 2, 1).Value = oSheet.Name
wsPrint.Paste wsPrint.Cells(iPics * 3 - 1, 1)
wsPrint.Rows(iPics * 3 - 1).RowHeight = _
wsPrint.Pictures(iPics).Height
End If
Next
wsPrint.PrintOut preview:=True
Application.DisplayAlerts = False
wsPrint.Delete
Application.DisplayAlerts = True
oSheets.Select
oActive.Activate
Application.ScreenUpdating = True


End Sub


HTH


Ivan


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.