excel 如何使用逐行复制整个数据并粘贴到另一个工作表

hivapdat 于 2023-02-05 发布在其他

关注(0)|答案(1)|浏览(356)

我试图将数据从一个工作表复制到另一个工作表。我想复制第一行并粘贴，然后复制第二行，然后粘贴到目标工作表中的下一个空行。实际上，我想使用逐行和循环复制数据，直到数据表中到达行的末尾。当宏到达行的末尾时，并且最后一行没有数据，则显示弹出完成消息。
我正在尝试以下代码，但它不能满足我的需要。任何建议和帮助将不胜感激。谢谢

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

excel

来源：https://stackoverflow.com/questions/75345419/how-to-copy-entire-data-using-row-by-row-and-paste-to-another-sheet

1条答案

按热度按时间

7gyucuyw1#

我想有两种方法可以解决你的任务（见第3部分）

Sub InsertData()

    Dim wsCopy As Worksheet, wsDest As Worksheet
    Dim lCopyLastRow As Long, lDestLastRow As Long, row 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
    For row = 5 To lCopyLastRow ' Cycle form 5th row (as well as you used range from A5) to last filled row (in A column)
        ' First: by copying data (not effective way, your solution is better)
        wsCopy.Range("A" & row).Copy wsDest.Range("D" & (lDestLastRow + (row - 5)))
        
        ' Second: by filling the data
        ' Like this: wsDest.Range("D" & (lDestLastRow + (row - 5))) = wsCopy.Range("A" & row)
    Next row

End Sub

赞(0）回复(0）举报 2023-02-05

我来回答

excel 如何使用逐行复制整个数据并粘贴到另一个工作表

1条答案

相关问题

热门标签

最新问答