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