Export Multiple Worksheets to PDF without Select

Question

I have 4 sheets, say "Sheet1", "Sheet2", "Sheet3" and "Sheet4".

For my PDF, I want to export the contents of Sheet3 and Sheet4, so it will be grouped into an Array as:

Sheets(Array("Sheet3", "Sheet4"));

Now, normally to export to PDF, I would Select this, then have

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:= _
"C:\filename.pdf", _
Quality:= xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

However, I want to have this Macro run totally in the background. If I was looking at Sheet1, and ran this macro, it will shift to Sheet3 because it is being selected which is disruptive to workflow.

I know how to do this for basic operations such as FillCell by declaring variables:

Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet2")
' modify ws

since this works behind the scenes.

In summary, I would like to export multiple worksheets (not all) to PDF without the need to use Select, Activate, or similar which will change the view of the workbook/worksheet I am currently looking at.

Duplicate of Use a method on multiple sheets simultaneously without using ActiveSheet or Select. — Comintern

izzymo izzymo · Accepted Answer · 2016-03-22T12:42:06

This should do the trick.

Sheets("Sheet 1").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:= _
"C:\filename.pdf", _
Quality:= xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Export Multiple Worksheets to PDF without Select

1 Answers