Printing Multiple Worksheets with different page settings from the same workbook

Occasional Visitor

I have a Workbook with multiple Worksheets. They have different page settings (like orientation, margins etc.). If you make a selection of the Sheets you want to print, Excel applies the same setting to each page according to the first page selected. Of course that is not what I want. Already talked to the support team at Microsoft and they told me that this is actually the case and that this isn't a native function and Mac even tough it work on Windows.

 

So I was thinking about a Macro that would provide me a workaround. For print I have found the following one which works great.

 

 

Sub print()

'
'
Dim sh As Worksheet
Dim ssh As Object
Set ssh = ActiveWindow.SelectedSheets
For Each sh In ssh
    With sh
        sh.PrintOut
    End With
Next sh
Sheets("Data").Select
End Sub

 

 

But now I have got the same Problem with exporting PDF's. My idea is to export every selected Worksheet as a single PDF and merging them afterwards. But there I have the problem that after exporting it changes the Pagesettings in my Workbook. The PDF's look great but not the Workbook. Can anybody help me with this or do you have another suggestion? Thanks in advance.

 

 

Sub save()

'
'
Dim path As String
path = Application.ThisWorkbook.path

Dim ssh As Sheets
Set ssh = ActiveWindow.SelectedSheets

Application.ScreenUpdating = False

Dim currentSheet As Object
Dim currentSheetOrientation As XlPageOrientation
For Each currentSheet In ssh
currentSheetOrientation = currentSheet.PageSetup.Orientation
currentSheet.Copy
ActiveSheet.PageSetup.Orientation = currentSheetOrientation
ActiveSheet.ExportAsFixedFormat xlTypePDF, Filename:=path & "/" & ActiveSheet.Name
ActiveWorkbook.Close SaveChanges:=False
Next currentSheet

Application.ScreenUpdating = True

End Sub

 

 

0 Replies