다른 통합 문서의 셀 참조를 기반으로 테이블을 필터링한 후 복사하기

Question

다른 통합 문서의 셀을 기반으로 테이블을 필터링하려고 합니다. 필터에 대한 참조는 통합 문서 "ReportWB" 및 "프로젝트 요약 세부 정보" 시트인 "I1" 셀("지역"에 포함된다고 생각함)에 있습니다. " 문자열? 사용자는 "Region"에 숫자를 입력한 다음 "DetailData" 통합 문서에서 테이블을 필터링한 다음 필터링된 범위를 복사해야 합니다. 지금은 자동 필터 부분에서 오류가 발생한다고 생각합니다. 올바르게 인용된 기준이 없습니다. 오류는 런타임 오류 '1004'입니다. 이 코드 줄에서 Range 클래스의 AutoFilter 메서드가 실패했습니다.With DetailData Sheets("Project Detail").Range("A1").AutoFilter field = 2, Criteria1:=ReportWB.Sheets("Project Summary Data").Range("I1") DetailData.Sheets("Project Detail").Range("A1").CurrentRegion.Copy

Dim region As String
Dim Report As Worksheet
Dim Data As Worksheet
Dim count_col As Integer
Dim count_row As Integer
Dim tbl As ListObject
Dim DetailData As Workbook
Dim ReportWB As Workbook

Set Report = ThisWorkbook.Sheets("Project Cost Report Summary")
Set Data = ThisWorkbook.Sheets("Project Summary Data")
region = Data.Range("I1").Text
Set tbl = Report.ListObjects("Table2")

'Clean up Summary Sheet
  Rows("9:3000").Select
    Range("A3000").Activate
    Selection.Delete Shift:=xlUp
    Range("B7").Select
    Selection.AutoFill Destination:=Range("Table2[Level]"), Type:=xlFillDefault
    Range("Table2[Level]").Select
    Columns("A:C").Select
    Selection.EntireColumn.Hidden = True

'Clear Contents of Project cost Report Detail Tab
Sheets("Project Detail").Select
Cells.Select
Selection.ClearContents

'Clear Contents of Project cost Report Summary Tab
 Sheets("Project Cost Report Summary").Select
    Range("Table2[[Task Code]:[Budget $''s]]").Select
    Selection.ClearContents
    
    
'## Open workbooks first:
Set DetailData = Workbooks.Open("C:\Users\sbossert\Documents\Macro Project Detail - All Data.xlsx")
Set ReportWB = Workbooks.Open("C:\Users\sbossert\Documents\Mid-Level Project Report - Template Filter data.xlsm")
'Now, copy what you want from Detail:

With DetailData
Sheets("Project Detail").Range("A1").AutoFilter field = 2, Criteria1:=ReportWB.Sheets("Project Summary Data").Range("I1")
DetailData.Sheets("Project Detail").Range("A1").CurrentRegion.Copy

'Now, paste to project detail worksheet:
ReportWB.Sheets("Project Detail").Range("A1").PasteSpecial

'Close x:
DetailData.Close

'determine the size of the range
Data.Activate
count_col = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

'filter data on Raw Data tab
Data.Range("A1").AutoFilter field:=1, Criteria1:=region

'Copy and Paste to Cost Report

Data.Range(Cells(2, 2), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
tbl.DataBodyRange(1, 4).PasteSpecial
Application.CutCopyMode = False

'Show Data and Remove the Filter
With Data
.ShowAllData
.AutoFilterMode = False

'Project Detail have only project values


'Refresh All Data
End With

ReportWB.RefreshAll


End With
End Sub```