EXCEL VBA 呼叫活頁簿會彈出陣列索引超出範圍

各位大大請教
小弟我在用VBA複製某活頁簿裡某工作表，該活頁簿是每日產生且檔名會加上當天日期，下列程式碼這要跑的時候會彈出錯誤視窗，陣列索引超出範圍，請問是哪邊出錯了

Dim ATT As String

ATT = Format(Date, "yyyymmdd") & "07.xlsx"

Workbooks("EXCEL_2_" & ATT).Sheets("ALL").copy

文章關鍵字

sjenator

113分

2樓

sjenator

個人積分：113分

文章編號：81675995

陣列範圍要縮小，太大了舊版的excel 才能這樣做

snare

2598分

3樓

snare

個人積分：2598分

文章編號：81677272

Sub test()

    Dim wb As Excel.Workbook, Filename As String, Target_path As String
    
    Target_path = "d:\excel\" '目錄名稱
    Filename = "EXCEL_2_" & Format(Date, "yyyymmdd") & "07.xlsx"
    
    If Dir(Target_path & Filename) = "" Then
        MsgBox "檔案不存在!", vbOKOnly, "Error"
        Exit Sub
    End If
        
    If checksheet(Replace(Filename, ".xlsx", "")) = True Then
        MsgBox "工作表重覆!", vbOKOnly, "Error"
        Exit Sub
    End If
        
    Set wb = Workbooks.Open(Target_path & Filename, , False)
    wb.Sheets("all").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ThisWorkbook.ActiveSheet.Name = Replace(Filename, ".xlsx", "")
    
    Application.DisplayAlerts = False
    wb.Close 1
    Application.DisplayAlerts = True
    
    Set wb = Nothing
    
End Sub


Function checksheet(Sheet_name As String) As Boolean
    Dim check As Range
    On Error Resume Next
    Set check = ThisWorkbook.Sheets(Sheet_name).Range("a1")
    If Err.Number <> 0 Then checksheet = False Else checksheet = True
    On Error GoTo 0
End Function