VBA导出多工作表为PDF时出现Subscript Out Of Range错误求助

Hey Steve, let's break down why you're hitting that error and get your code working smoothly!

The Root Cause #

Your current code builds PDFarray as a single string that looks like "Sheet1", "Sheet2" — but when you pass that to Worksheets(Array(PDFarray)), Excel treats the entire string as one worksheet name, not a list of separate names. Since there's no worksheet named "Sheet1", "Sheet2", it throws the Subscript Out Of Range error.

Step-by-Step Fixes #

Here's how to adjust your code to work correctly:

1. Use an actual array instead of string concatenation
   Instead of building a comma-separated string, create a proper string array to hold each worksheet name. This lets Excel recognize each entry as a distinct sheet.
2. Fix typos
   You have a typo: PFDLoc should be PDFLoc (the variable you defined earlier). This would have caused a save path error once you fixed the array issue.
3. Avoid Select/Activate
   These are unreliable in VBA — directly reference worksheets instead of selecting them first, which makes your code more stable and faster.

Revised Working Code #

Sub B_PDFs()
    Dim PDFarray() As String, PDFName As String
    Dim PDFSheetCount As Long, x As Long
    Dim controlSht As Worksheet
    
    ' Set direct reference to Control worksheet (no need to select)
    Set controlSht = ThisWorkbook.Sheets("Control")
    
    PDFName = controlSht.Range("A20").Value
    PDFSheetCount = controlSht.Range("J" & controlSht.Rows.Count).End(xlUp).Row
    
    ' Resize array to match the number of worksheets we need to export
    ReDim PDFarray(1 To PDFSheetCount - 1) ' We start looping at x=2, so adjust index
    
    ' Populate the array with worksheet names from column J
    For x = 2 To PDFSheetCount
        PDFarray(x - 1) = controlSht.Cells(x, 10).Value
    Next x
    
    ' Export the selected worksheets to PDF
    ThisWorkbook.Worksheets(PDFarray).ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\" & PDFName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End Sub

Key Notes #

• Array Handling: By using ReDim to size the array and assigning each worksheet name directly, we pass a real array of sheet names to Worksheets(), which Excel can correctly interpret.
• No More Select: We reference the Control sheet directly with Set controlSht = ThisWorkbook.Sheets("Control"), eliminating flaky selection-based code.
• Cleaner Path: The Filename parameter uses ThisWorkbook.Path & "\" & PDFName to avoid relying on a separate path variable (though you could still use your PDFLoc if you fix the typo!).

内容的提问来源于stack exchange，提问作者SteveJ