MrExcel Consulting
Your One Stop for Excel Tips & Solutions

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.

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
wsPrint.Cells(iPics * 3 - 2, 1).Value = oSheet.Name
wsPrint.Paste wsPrint.Cells(iPics * 3 - 1, 1)
wsPrint.Rows(iPics * 3 - 1).RowHeight = _
End If
wsPrint.PrintOut preview:=True
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub