使用VBA选择一系列工作表

Question

我想用 VBA 编写一个打印宏，它将打印到 pdf 中，包括一个名为"开始"的工作表和一个名为"结束"的工作表之间的所有工作表。 我不想使用所有工作表名称，因为如果在"开始"和"结束"之间插入新工作表，我希望代码正常工作。 我基本上只需要选择工作表的代码。 任何帮助将不胜感激。

Answer 1

请尝试以下操作;

Sub SaveBeginningToEnd()
Dim wsBeginning As Worksheet
Dim wsEnd As Worksheet
On Error Resume Next
Set wsBeginning = ThisWorkbook.Worksheets("Beginning")
Set wsEnd = ThisWorkbook.Worksheets("End")
On Error GoTo 0
If wsBeginning Is Nothing Then
Debug.Print "Cannot find worksheet Beginning"
Exit Sub
ElseIf wsEnd Is Nothing Then
Debug.Print "Cannot find worksheet End"
Exit Sub
ElseIf wsBeginning.Index > wsEnd.Index Then
Debug.Print "Beginning Worksheet is after End Worksheet."
Exit Sub
End If
Dim varSheets() As Long
ReDim varSheets(wsEnd.Index - wsBeginning.Index)
For i = wsBeginning.Index To wsEnd.Index
varSheets(i - wsBeginning.Index) = i
Next i
ThisWorkbook.Sheets(varSheets).Select
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)
fd.AllowMultiSelect = False
fd.Title = "Save As PDF"
Dim intIndex As Integer
For intIndex = 1 To fd.Filters.Count
If fd.Filters(intIndex).Description = "PDF" Then fd.FilterIndex = intIndex
Next
If fd.Show = -1 Then
ThisWorkbook.ExportAsFixedFormat xlTypePDF, fd.SelectedItems(1)
End If
End Sub