Excel VBA 将多个工作表从一个范围保存到一个 PDF

Question

我目前正在处理工作簿，其中工作表(“STAM-Filialen”)的 A:A 列中几乎所有其他工作表的名称都是。我只想要在单个 PDF 中以 column("A:A") 命名的那些工作表。我使用的代码使它成为每个工作表的单独文件。是否可以使用某种相同的代码将其保存为单个 PDF？

Dim myCell As Range
Dim lastCell As Long
Dim PathName As String
lastCell = lastRow("STAM-Filialen")
PathName = Range("I10").Value

Worksheets("STAM-Filialen").Activate
For Each myCell In ThisWorkbook.Worksheets("STAM-Filialen").Range("A2:A" & lastCell).Cells
    Dim wksName As String
    wksName = myCell.Text
    ThisWorkbook.Worksheets(wksName).Range("A1:P60").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PathName & "DispoPlan.Filiaal " & wksName & ".PDF"
Next

Answer 1

我建议将所有值移动到一张纸上打印。然后在完成后删除此临时工作表。

这是一个将每个工作表中的每个范围并排放置在新工作表中的示例。

Option Explicit

Public Sub CreateSinglePDF()
    Dim ws As Range: Set ws = ThisWorkbook.Sheets(1).Range("A1:A4")
    Dim rangeDict As Object: Set rangeDict = CreateObject("Scripting.Dictionary")
    Dim cell As Range

    For Each cell In ws
        If Not rangeDict.exists(cell.Value) And cell.Value <> "" Then
            rangeDict.Add cell.Value, ThisWorkbook.Sheets(cell.Value).Range("A1:A5")
        End If
    Next

    Dim printsheet As Worksheet
    Set printsheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

    Dim key As Variant
    Dim i As Long: i = 1
    For Each key In rangeDict
        printsheet.Range(printsheet.Cells(1, i), printsheet.Cells(5, i)).Value = rangeDict(key).Value
        i = i + 1
    Next

    printsheet.UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\users\ryan\desktop\ExampleFile.pdf"
    printsheet.Delete
End Sub