引用对象

• 使用Option Explicit。
• 尝试一次引用一个对象，即使用变量。

Option Explicit

Sub ExportIssuesLog()

    Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    ' Make sure the workbook is active, or the ranges will fail.
    If Not swb Is ActiveWorkbook Then swb.Activate
    If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub ' not a worksheet
    Dim sws As Worksheet: Set sws = ActiveSheet
    
    Dim dPath As String: dPath = swb.Path & "\" & Range("Proj_no").Value _
        & "_" & Range("Client_short").Value & "_" _
        & Range("Facility_short").Value & "_IssuesLog_REV-" & Range("B4").Value
    
    Dim Answer As Long
    Answer = MsgBox("Do you want to export the issues log?" _
        & Chr(13) & Chr(13) & Chr(10) & "Note: This macro automatically " _
        & "overwites versions with the same Revision Number. " _
        & "Please ensure the revision number is updated correctly.", _
        vbQuestion + vbYesNo)
    If Answer = vbNo Then Exit Sub ' canceled
    
    sws.Copy
    
    Dim dwb As Workbook: Set dwb = Workbooks(Workbooks.Count)
    Dim dws As Worksheet: Set dws = dwb.Sheets(1)
        
    Dim shp As Shape
    For Each shp In dws.Shapes
        If shp.AutoShapeType = msoShapeStyleMixed Then shp.Delete
    Next shp
    
    Application.DisplayAlerts = False ' overwrite without conpfirmation
        dwb.SaveAs Filename:=dPath
    Application.DisplayAlerts = True
    dwb.Close SaveChanges:=False
    
    MsgBox "Issues Log exported.", vbInformation
    
End Sub