I have seen this question but it doesn't quite answer my question – excel vba not exporting pagesetup to pdf correctly
I have the same problem of the specified ranges in each sheet not being exported when utilising code to create the .pdf output. Everything on each sheet is exported so each worksheet is spread across two or more pages. The print ranges for each sheet are set up to print the specified area onto one sheet.
I have tried to adapt the code in the link but it doesn't seem to work with multiple sheets.
The code I am attempting to use in its unadapted form
Sub ClientPDFOutput()
If Sheets("File Data").Range("FD_FileName") = "" Then
' MsgBox ("Save the file before exporting to a .pdf fomrat"), vbInformation, "Save File"
' Exit Sub
Else
End If
ActiveSheet.Unprotect Password:=strPassword
Range("UI_Status") = "Creating client PDF output - Please wait"
SelectSheets
Application.ScreenUpdating = False
Sheets(arrSheets).Select
strFilename = "Test"
Selection.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=ActiveWorkbook.Path & "\" & strFilename & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=True, _
OpenAfterPublish:=False
Sheets("User Input").Select
Range("UI_Status") = "Client .pdf output created and saved"
ActiveSheet.Protect Password:=strPassword
Application.ScreenUpdating = True
MsgBox ("The client output in .pdf format has been created and saved"), vbInformation, ".pdf Created"
End Sub
AND
Sub SelectSheets()
Dim rngSheets As Range
Set rngSheets = Sheets("File Data").Range("D_OutputSheets")
If rngSheets.Count = 1 Then
arrSheets = rngSheets.Value2
Else
arrSheets = Application.Transpose(rngSheets.Value2)
End If
End Sub
After a bit more experimenting I established that my print ranges on each of the pages was off so corrected these.
I added code to select the print range of each sheet before all being selected as part of the sheet array, but the print range in the first sheet of the array is being duplicated across all sheets. So if the range in sheet 1 is B4:P61 and sheet 2 print range is B4:M48, sheet 2 is having B4:P61 selected when the array of sheets is selected.
This prints out the selected ranges which is correct for sheet 1 but wrong for the rest of the sheets.
When I do this manually by selecting all the sheets, File, Export then all the sheets print ranges are exported so why when this is recorded and put into a routine it is being ignored?
Best Answer
Please try to change
IgnorePrintAreas
property.