VBA使用lastrow范围将数据从一个工作簿复制到另一工作簿 [英] VBA Copying data from one workbook to another using lastrow ranges

问题描述

在工作簿中带有数据透视表的工作表上有一个按钮，名为"Warranty Template.xlsm".我希望该按钮复制从A5开始的第一列数据，并将该列粘贴到另一个名为"QA Matrix Template.xlsm"的工作簿中.我希望复制的数据在该列的最后空白行结束，并且我希望粘贴的范围也要粘贴到从D12开始的第一行空白.

I have a button on a sheet with a PivotTable in a workbook called "Warranty Template.xlsm". I want this button to copy the first column of data, which starts at A5, and paste this column into another workbook called "QA Matrix Template.xlsm". I want the copied data to end at the last blank row of the column and I want the range in which it is pasted to also be pasted on the first blank row starting from D12 down.

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 变量.如果我设置静态范围，但我需要这些范围是动态的，则代码可以工作.

I am getting Subscript Error: '1004' and I am not sure why. It has to do with my lCopyLastRow & lDestLastRow variables. The code works if I set static ranges but I need these ranges to be dynamic.

推荐答案

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

这篇关于VBA使用lastrow范围将数据从一个工作簿复制到另一工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！