基于TRUE值筛选工作表并导出为单PDF的Excel VBA开发问询

Hey there! Let's build that VBA macro to export your selected worksheets into a single PDF. Based on your requirements, here's a complete, robust solution that handles dynamic worksheet counts and validates your "PDF" sheet settings:

Sub PDFCreator()
    Dim wsPDF As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim sheetNames As Collection
    Dim ws As Worksheet
    Dim pdfPath As String
    Dim pdfName As String
    
    ' 设置PDF文件名和保存路径（可按需修改）
    pdfName = "Combined_Export.pdf"
    ' 默认保存到工作簿所在文件夹，也可指定固定路径如"C:\Exports\"
    pdfPath = ThisWorkbook.Path & "\" & pdfName
    
    On Error GoTo Cleanup
    ' 定位到"PDF"工作表
    Set wsPDF = ThisWorkbook.Worksheets("PDF")
    
    ' 获取D列最后一行的有效数据
    lastRow = wsPDF.Cells(wsPDF.Rows.Count, "D").End(xlUp).Row
    
    ' 初始化集合存储符合条件的工作表（应对数量不固定的场景）
    Set sheetNames = New Collection
    
    ' 遍历D列的工作表名称，检查F列是否为TRUE
    For i = 2 To lastRow ' 假设第1行是表头，从第2行开始遍历数据
        Dim targetSheetName As String
        targetSheetName = Trim(wsPDF.Cells(i, "D").Value)
        
        ' 校验F列值为TRUE，且目标工作表存在
        If UCase(wsPDF.Cells(i, "F").Value) = "TRUE" Then
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(targetSheetName)
            On Error GoTo Cleanup
            
            If Not ws Is Nothing Then
                sheetNames.Add ws
                Set ws = Nothing ' 释放对象资源
            Else
                MsgBox "工作表 '" & targetSheetName & "' 不存在，已跳过该条目。", vbExclamation
            End If
        End If
    Next i
    
    ' 若有符合条件的工作表，执行导出
    If sheetNames.Count > 0 Then
        ' 将集合转换为数组（ExportAsFixedFormat要求数组参数）
        Dim exportSheets() As Worksheet
        ReDim exportSheets(1 To sheetNames.Count)
        
        For i = 1 To sheetNames.Count
            Set exportSheets(i) = sheetNames(i)
        Next i
        
        ' 选中所有目标工作表
        exportSheets(1).Select
        For i = 2 To sheetNames.Count
            exportSheets(i).Select False ' 多选模式，不取消之前的选中
        Next i
        
        ' 导出为PDF
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=pdfPath, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True ' 导出后自动打开PDF，可改为False关闭此功能
        
        MsgBox "PDF已成功导出到：" & vbCrLf & pdfPath, vbInformation
    Else
        MsgBox "没有找到需要导出的工作表，请检查'PDF'工作表中的设置。", vbExclamation
    End If
    
Cleanup:
    ' 清理对象，释放资源
    Set wsPDF = Nothing
    Set sheetNames = Nothing
    If Err.Number <> 0 Then
        MsgBox "导出过程中出错：" & Err.Description, vbCritical
    End If
End Sub

Key Details & Explanations: #

• Dynamic Worksheet Handling: We use a Collection instead of a fixed array to gather valid worksheets—this lets you add/remove entries in the "PDF" sheet without updating the code.
• Error Prevention:
  • We check if the target worksheet exists before adding it to our export list, so you get a clear warning if a sheet name is misspelled or missing.
  • The error handler at the end catches unexpected issues and cleans up objects properly.
• Export Logic:
  • We select all target sheets first (using Select False to keep previous selections), then export the entire selection as one PDF.
  • The OpenAfterPublish parameter is set to True for convenience—flip it to False if you don't want the PDF to open automatically.
• Customization Tips:
  • Adjust the pdfName variable to use your preferred filename.
  • Modify pdfPath to save to a fixed directory (e.g., pdfPath = "C:\MyPDFExports\" & pdfName).
  • If your "PDF" sheet data starts at a row other than 2, update the For i = 2 To lastRow line to match your starting row.

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