Excel VBA中PrintStage1的ExportAsFixedFormat漏打最后工作表问题

我有两个功能完全一致的VBA Sub过程，分别针对不同工作表执行以下操作：取消7张工作表隐藏、全选、导出PDF、重新隐藏非当前工作表。其中Sub PrintStage2()可正常导出所有选中工作表，但Sub PrintStage1()无法导出最后一张“S1 ELT Comments”工作表。

• 该工作表是“S2 ELT Comments”的重命名副本，包含完全相同的页面打印设置。
• 使用Excel版本：Microsoft® Excel® for Microsoft 365 MSO (Version 2409 Build 16.0.18025.20030) 64-bit
• 已尝试方案：移除PrintStage1部分工作表、复制“S2 ELT Comments”替代目标表、调整打印设置、切换文件存储位置等，但问题仍存在。
• 期望：PrintStage1生成包含所有选中工作表的PDF

代码示例 #

PrintStage1 代码 #

Sub PrintStage1()

Dim S1ProjectName As String
Dim ary As String
Dim strTimeStamp1 As String

Application.ScreenUpdating = False
'Application.EnableEvents = False

'Create dateStamp
strTimeStamp1 = Format(Now(), "yyyy-mm-dd")

Sheets("S1 Project Leader").Visible = True
Sheets("S1 Risk Summary").Visible = True
Sheets("S1 Financials").Visible = True
Sheets("S1 Appendices").Visible = True
Sheets("S1 ELT Comments").Visible = True
Sheets("G1 DASHBOARD P1").Visible = True
Sheets("G1 DASHBOARD P2").Visible = True

ThisWorkbook.Sheets(Array("G1 DASHBOARD P1", _
  "G1 DASHBOARD P2", _
  "S1 Project Leader", _
  "S1 Risk Summary", _
  "S1 Financials", _
  "S1 Appendices", _
  "S1 ELT Comments")).Select

S1ProjectName = Range("S1_Value_ProjectName")

ActiveSheet.ExportAsFixedFormat _
  Type:=xlTypePDF, FileName:= _
  "C:\Temp\GatePapers\" & S1ProjectName & " - Gate 1 " & strTimeStamp1, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=True

Worksheets("HOME").Activate

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws
End Sub

PrintStage2 代码 #

Sub PrintStage2()

Dim S2ProjectName As String
Dim ary As String
Dim strTimeStamp2 As String

Application.ScreenUpdating = False
'Application.EnableEvents = False

'Create dateStamp
strTimeStamp2 = Format(Now(), "yyyy-mm-dd")


Sheets("S2 Project Leader").Visible = True
Sheets("S2 Risk Summary").Visible = True
Sheets("S2 Financials").Visible = True
Sheets("S2 Appendices").Visible = True
Sheets("S2 ELT Comments").Visible = True
Sheets("G2 DASHBOARD P1").Visible = True
Sheets("G2 DASHBOARD P2").Visible = True



ThisWorkbook.Sheets(Array("G2 DASHBOARD P1", _
  "G2 DASHBOARD P2", _
  "S2 Project Leader", _
  "S2 Risk Summary", _
  "S2 Financials", _
  "S2 Appendices", _
  "S2 ELT Comments")).Select


S2ProjectName = Range("S2_Value_ProjectName")

ActiveSheet.ExportAsFixedFormat _
  Type:=xlTypePDF, FileName:= _
  "C:\Temp\GatePapers\" & S2ProjectName & " - Gate 2 " & strTimeStamp2, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=True


Worksheets("HOME").Activate

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws


End Sub

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