如何在Excel宏导出的PDF中重复指定工作表页面

要让指定工作表在最终PDF中重复出现，最直接的方法是临时复制目标工作表多次，导出完成后再删除这些副本，不会影响原工作簿的结构。以下是修改后的完整宏代码：

Sub ExportToPDF()
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim mds As Worksheet
    Set mds = wb.Sheets("Master Data Sheet")
    
    Dim DefaultSheets, SelectedSheets As Variant
    DefaultSheets = Array("Proforma Invoice", "SLI", "VGM Form", "Commercial Invoice", "Cert of Origin", "Packing List")
    
    ' 定义需要重复的工作表名称和重复次数
    Dim RepeatSheetName As String
    Dim RepeatCount As Integer
    RepeatSheetName = "Proforma Invoice" ' 要重复的工作表名
    RepeatCount = 3 ' 重复次数（原表+3次副本=总共4次出现）
    
    Dim Country, Company, CurrDate, OrderNo, FilePath As String
    Country = mds.Range("E49").Value
    Company = mds.Range("D36").Value
    CurrDate = mds.Range("E46").Value
    OrderNo = mds.Range("E39").Value
    
    ' 隐藏不需要导出的工作表
    For Each Sheet In Array("Master Data Sheet", "Multi Order Queries", "Packing List Query")
        wb.Worksheets(Sheet).Visible = xlSheetHidden
    Next
    
    ' 临时复制需要重复的工作表
    Dim i As Integer
    For i = 1 To RepeatCount
        wb.Worksheets(RepeatSheetName).Copy After:=wb.Worksheets(wb.Worksheets.Count)
    Next i
    
    FilePath = GetFolder()
    
    ' 导出所有可见工作表为PDF
    wb.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        IncludeDocProperties:=True, _
        Filename:=FilePath + "\" + Company + "_" + OrderNo + "_" + CurrDate + ".pdf", _
        OpenAfterPublish:=True
        
    ' 删除临时创建的重复工作表副本
    Dim ws As Worksheet
    Application.DisplayAlerts = False ' 关闭删除确认提示
    For Each ws In wb.Worksheets
        ' 判断是否是重复生成的副本（名称格式为"原表名 (数字)"）
        If InStr(ws.Name, RepeatSheetName & " (") > 0 Then
            ws.Delete
        End If
    Next ws
    Application.DisplayAlerts = True ' 恢复提示
    
    ' 恢复隐藏的工作表
    For Each Sheet In Array("Master Data Sheet", "Multi Order Queries", "Packing List Query")
        wb.Worksheets(Sheet).Visible = xlSheetVisible
    Next
    
    mds.Activate
End Sub

辅助函数保持不变：

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

关键说明 #

1. 重复规则设置：修改RepeatSheetName和RepeatCount即可调整要重复的工作表和次数。比如设置RepeatCount=3，原工作表会加上3个副本，最终PDF中该表总共出现4次。
2. 临时副本处理：导出完成后，通过判断工作表名称是否包含"原表名 (数字)"的格式，自动删除所有临时副本，避免污染原工作簿。
3. 提示关闭：删除副本时关闭Application.DisplayAlerts，避免弹出删除确认对话框，提升宏的执行流畅度。

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