How do I “sheetwise” save or export an Excel workb

Question

The problem with the code I have is that either it saves all the pages in all the worksheets in the workbook or it saves only the pages in the range specified by the "from" and "to" arguments.

The excel files I am working with have 7 worksheets, and each worksheet can have any amount of pages. If I specify that I want to export "from 1 to 4", for example, then only the first 4 pages of the first worksheet would be exported as a PDF document, not all the pages of the first 4 worksheets.

Can someone tell me how to achieve what I'm trying to do? The code below is not what I want:

application.ActiveWorkbook.ExportAsFixedFormat(
      Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
      path,
      Excel.XlFixedFormatQuality.xlQualityStandard,
      true,
      true,
      1,
      4,
      false,
      Missing.Value);

Answer 1

Public Sub Export(ParamArray ToPrint() As Variant)

'hide all sheets
For Each Sheet In ActiveWorkbook.Sheets

    Sheet.Hide

Next Sheet

'unhide sheets to print
For Each pageNo In ToPrint

ActiveWorkbook.Sheets(pageNo).Show

Next pageNo

'do the export
    Worksheet.ExportAsFixedFormat _
        Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, _
        Path, _
        Excel.XlFixedFormatQuality.xlQualityStandard, _
        True, _
        True, _
        Nothing, _
        Nothing, _
        False, _
        Missing.Value

'unhide all sheets
For Each Sheet In ActiveWorkbook.Sheets

    Sheet.Show

Next Sheet

End Sub

Call it by passing in a comma seperated list of sheets to export

Export 1, 2, 4

Answer 2

Here's the answer: ActiveSheet.PageSetup.Pages.Count

Answer 3

If you use ActiveSheet.ExportAsFixedFormat after selecting multiple sheets I think it will do what you want (as long as your print areas are set up on each of the selected sheets).

A raw recorded macro along those lines:

    Sheets(Array("Sheet1", "Sheet2")).Select

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Documents and Settings\yourusername\Desktop\Book1.pdf", _  
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False