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
