Ignore Print Area

[ExcelNovice17]

Hi,

I am a novice in VBA but my work is demanding more and more. So my issue is that i am trying to save multiple worksheets in one PDF but when i open the PDF, the output is ignoring my customed print area. I wanted to know how can i deflect that so that both my printed hardcopy and my saved PDF come out the same. Below is my code for the save-to-pdf:

Sub Save()
Sheets(Array("worksheetA", "worksheetB", "worksheetC", "worksheetD", "worksheetE")).Select
Sheets("worksheetA").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A12:C12"), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=True
End Sub

 

[bobsan42]

You are on the right path.

IgnorePrintAreas Optional Variant If set to True, ignores any print areas set when publishing. If set to False, will use the print areas set when publishing.

So simply set IgnorePrintAreas:=False and it should do it.

Do not hesitate to try code changes and examine the outcome.
Also some advice beside the point:
avoid selecting/activating if not absolutely necessary (which is relatively rare) - sheet can be exported without activating:

Sheets("worksheetA").ExportAsFixedFormat Type:=xlTypePDF, Filename:=[COLOR=#ff0000]Sheets("worksheetA").[/COLOR]Range("A12:C12"), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=[B]False[/B], OpenAfterPublish:=True

It will still print all selected sheets.
Also if you want to export all sheets in a workbook you can do it like this:

Thisworkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[COLOR=#ff0000]Sheets("worksheetA")[/COLOR].Range("A12:C12"), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=[B]False[/B], OpenAfterPublish:=True

You must also specify on which sheet is the range containing the file name (in red above).

 

[ExcelNovice17]

You are on the right path.


So simply set IgnorePrintAreas:=False and it should do it.

Do not hesitate to try code changes and examine the outcome.
Also some advice beside the point:
avoid selecting/activating if not absolutely necessary (which is relatively rare) - sheet can be exported without activating:

Sheets("worksheetA").ExportAsFixedFormat Type:=xlTypePDF, Filename:=[COLOR=#ff0000]Sheets("worksheetA").[/COLOR]Range("A12:C12"), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=[B]False[/B], OpenAfterPublish:=True

It will still print all selected sheets.
Also if you want to export all sheets in a workbook you can do it like this:

Thisworkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[COLOR=#ff0000]Sheets("worksheetA")[/COLOR].Range("A12:C12"), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=[B]False[/B], OpenAfterPublish:=True

You must also specify on which sheet is the range containing the file name (in red above).

Hi,
Thank you a lot for helping me.

Despite putting "IgnorePrintAreas:=False", it is still not working and publishing the PDF with extra lines.

I have specify the worksheets to print as the excel file contains around 9 pages and only 5 are needed for printing and saving.

Thank you again.

 

[ExcelNovice17]

Can i set a specific ignore area for each worksheet that i have to save?

 

[bobsan42]

Have you set any print area on EVERY sheet?

Print area has to be set for every sheet that you want to publish.
Otherwise in the code you can (have to) specify the print area for every sheet.

 

[ExcelNovice17]

Have you set any print area on EVERY sheet?

Print area has to be set for every sheet that you want to publish.
Otherwise in the code you can (have to) specify the print area for every sheet.

Yes, I have set print area on every sheet.

 

[bobsan42]

I can confirm that (for me at least) when IgnorePrintAreas is set to False all defined PrintAreas are respected when published as PDF.

Try it manually through the Save as menu and check the output:
(in the save as Dialog click on Options ... - inspect all selected options and see if anything looks out of place or experiment several times with different options to see if the outcome is the same)