我在名为“Warranty Template.xlsm”的工作簿中带有数据透视表的工作表上有一个按钮。我希望此按钮复制从 A5 开始的第一列数据，并将该列粘贴到另一个名为“QA Matrix Template.xlsm”的工作簿中。我希望复制的数据在该列的最后一个空白行处结束，并且希望将其粘贴的范围也粘贴到从 D12 开始的第一个空白行上。
Sub InsertData()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
'Set variables for copy and destination sheets
Set wsCopy = Workbooks("Warranty Template.xlsm").Worksheets("PivotTable")
Set wsDest = Workbooks("QA Matrix Template.xlsm").Worksheets("Plant Sheet")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A5").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "D12").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("A5" & lCopyLastRow).Copy _
wsDest.Range("D12" & lDestLastRow)
End Sub
我收到下标错误：“1004”，我不知道为什么。这与我的lCopyLastRow & lDestLastRow变量。如果我设置静态范围，则代码可以工作，但我需要这些范围是动态的。
Sub InsertData()
Dim wsCopy As Worksheet, wsDest As Worksheet
Dim lCopyLastRow As Long, lDestLastRow As Long
'Set variables for copy and destination sheets
Set wsCopy = Workbooks("Warranty Template.xlsm").Worksheets("PivotTable")
Set wsDest = Workbooks("QA Matrix Template.xlsm").Worksheets("Plant Sheet")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, 1).End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, 4).End(xlUp).Offset(1,0).Row
'3. Copy & Paste Data
wsCopy.Range("A5:A" & lCopyLastRow).Copy _
wsDest.Range("D" & lDestLastRow)
End Sub
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
