VBA for PDF'ing Multiple Sheets

Question

Hi.

I have some VBA code which works great when I want to PDF one worksheet from a workbook and name it based on a field in the spreadsheet.

I am wondering though how I can alter the code to PDF two of the sheets into one document - the second sheet is called Comments.  I think I need to use an array but have tried a few things to no avail.

Does anyone have any suggestions that might help?

Thanks.

Elizabeth.

The code I have currently is included below:

Option Explicit

Private Sub PDF()

Application.ScreenUpdating = False

Dim wsA As Worksheet

Dim wbA As Workbook

Dim wsB As Worksheet

Dim strName As String

Dim strPath As String

Dim strFile As String

Dim strPathFile As String

Dim rng As String

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Visible = xlSheetVisible

Next ws

On Error GoTo errHandler

Set wbA = ActiveWorkbook

Set wsA = Worksheets("Summary")

Set wsB = Worksheets("Lookup Table")

wsB.Range("B230").Value = 1

strName = wsB.Range("c230").Value

strPath = "C:\Users\Elizabeth\Desktop\Files"

strFile = strName & ".pdf"

strPathFile = strPath & strFile

wsA.ExportAsFixedFormat _

        Type:=xlTypePDF, _

        Filename:=strPathFile, _

        Quality:=xlQualityStandard, _

        IncludeDocProperties:=True, _

        IgnorePrintAreas:=False, _

        OpenAfterPublish:=False

Do

wsB.Range("B230").Value = wsB.Range("B230").Value + 1

strName = wsB.Range("C230").Value

strPath = "C:\Users\Elizabeth\Desktop\Files"

strFile = strName & ".pdf"

strPathFile = strPath & strFile

wsA.ExportAsFixedFormat _

        Type:=xlTypePDF, _

        Filename:=strPathFile, _

        Quality:=xlQualityStandard, _

        IncludeDocProperties:=True, _

        IgnorePrintAreas:=False, _

        OpenAfterPublish:=False

Loop While wsB.Range("B230").Value < 227

wsB.Range("B230").Value = 1

exitHandler:

    Exit Sub

errHandler:

    MsgBox "Could not create PDF file"

    Resume exitHandler

Application.ScreenUpdating = True

End Sub

Answer 1

Does this do what you want?

Private Sub PDF()

    Dim wbA As Workbook

    Dim wsB As Worksheet

    Dim strName As String

    Dim strPath As String

    Dim strFile As String

    Dim strPathFile As String

    Dim rng As String

    Dim ws As Worksheet

    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets

        ws.Visible = xlSheetVisible

    Next ws

    On Error GoTo errHandler

    Set wbA = ActiveWorkbook

    Set wsB = Worksheets("Lookup Table")

    wsB.Range("B230").Value = 0

    Do

        wsB.Range("B230").Value = wsB.Range("B230").Value + 1

        strName = wsB.Range("C230").Value

        strPath = "C:\Users\Elizabeth\Desktop\Files"

        strFile = strName & ".pdf"

        strPathFile = strPath & strFile

        Worksheets(Array("Summary", "Comments")).ExportAsFixedFormat _

            Type:=xlTypePDF, _

            Filename:=strPathFile, _

            Quality:=xlQualityStandard, _

            IncludeDocProperties:=True, _

            IgnorePrintAreas:=False, _

            OpenAfterPublish:=False

    Loop While wsB.Range("B230").Value < 227

    wsB.Range("B230").Value = 0

exitHandler:

    Application.ScreenUpdating = True

    Exit Sub

errHandler:

    MsgBox "Could not create PDF file"

    Resume exitHandler

End Sub

Answer 2

Sorry about that. Change that part of the code to

        Worksheets(Array("Summary", "Comments")).Select

        ActiveSheet.ExportAsFixedFormat _

            Type:=xlTypePDF, _

            Filename:=strPathFile, _

            Quality:=xlQualityStandard, _

            IncludeDocProperties:=True, _

            IgnorePrintAreas:=False, _

            OpenAfterPublish:=False

Answer 3

Unfortunately not - it goes the error handler when it reaches the following bit:

Worksheets(Array("Summary", "Comments")).ExportAsFixedFormat _

            Type:=xlTypePDF, _

            Filename:=strPathFile, _

            Quality:=xlQualityStandard, _

            IncludeDocProperties:=True, _

            IgnorePrintAreas:=False, _

            OpenAfterPublish:=False