excel - VBA使用lastrow范围将数据从一个工作簿复制到另一个工作簿
问题描述
我在名为“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
推荐阅读
- python - 熊猫列的条件竞争
- node.js - 阿波罗 | 如何从服务器通知订阅?
- airflow - 对象的气流分支错误不可迭代
- javascript - 将 css 捆绑到 js 时如何让 webpack 最小化器工作
- r - R dplyr 条件变异与 group_by
- javascript - 使用循环键计算对象值
- git - 无法建立真实性主机 github.com (140.82.xxx.x) - RSA 密钥指纹为 SHA256:nThbgh78...您确定要继续吗?
- youtube-dl - 尝试使用 Youtube-DL 下载视频
- javascript - IndexedDB - IDBObjectStore.get 与 IDBKeyRange - 请求只返回单个对象?
- python - 检查文件是否正在python中复制?