☰

Excel 宏：将工作表中的值粘贴到表格中出现错误

tag-icon • tag-icon microsoft-excel macros
Excel 宏：将工作表中的值粘贴到表格中出现错误

我正在尝试从工作表“项目摘要数据”复制数据并将其粘贴到工作表“项目成本报告摘要”上的表格中。我对宏还很陌生，所以我不确定我在这里做错了什么，但错误似乎就在这一部分tbl.Range("D7").Paste。

我需要将数据粘贴到“项目成本报告摘要”中的单元格“D7”中，从技术上讲，该单元格是表格中的第一行数据（无标题）和表格的第 4 列。

我收到的错误是运行时错误“1004”：无法获取范围类的选择属性

Sub filter_copy_paste()

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


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")

'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.Range("D7").Paste
Application.CutCopyMode = False

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

End With

End Sub

相关内容