在第一个空白行循环中复制和粘贴 [英] Copy and Paste in First Blank Row Loop

问题描述

在工作表1中，我在列中有数据.在工作表2中，我有专门用于复制和粘贴到工作表1的空白行中的数据.我试图循环工作表2中的数据，并将其复制到工作表1的以下行中(24、25、26，等)我在粘贴部分遇到了麻烦.我还需要代码统一，以便我可以修改工作表2并运行宏而无需进行任何更改.我正在使用下面的代码.

In Sheet 1 I have data in columns. In worksheet 2 I have data that is designated specifically for copying and pasting into the blank rows of Sheet 1. I'm trying to loop the data in Sheet 2 and copy it into Sheet 1 in the following rows (24,25,26,etc.) I'm having trouble with the pasting part. I also need the code to be uniform so that I can modify sheet 2 and run the macro without changing anything. I'm using the code below.

Dim LastRow2 As Long
Dim BlankRow As Long
BlankRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
For i = BlankRow To 50
    For j = 2 To LastRow2
        Sheets("Sheet2").Range("A" & j).Copy
        Sheets("Sheet1").Range("A" & i).PasteSpecial
    Next j
Next i

我在下面放置了一个非常简化的示例，目的只是为了帮助您了解我到底想做什么. 在电子表格中，工作表1中的行数为数千，工作表2中的行数大约为30.因此，对我来说很重要的一点是，我要使用工作表1中的第一个空白行作为起点来对其进行循环.

I've placed a very simplified example below as well just to help give an idea of what exactly I'm trying to do. The rows in Sheet 1 are in the thousands in my spreadsheet, and Sheet 2 can get to be around 30. So it's important to me that I loop it using the first blank row in Sheet 1 as the starting point.

Sheet 1
        A
1       x
2       x
3       x
4       x
5       x
6       x


Sheet 2
        A
1       Headings
2       Tyler
3       Bill
4       Rob
5       Dennis

推荐答案

您不需要嵌套循环.您只需要1.像这样的东西:

You don't need a nested loop. You just need to 1. Something like:

Dim LastRow2 As Long, BlankRow As Long, j As Long
BlankRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
For j = 2 To LastRow2
    Sheets("Sheet2").Range("A" & j).Copy
    Sheets("Sheet1").Range("A" & BlankRow).PasteSpecial xlPasteValues
    BlankRow = BlankRow + 1
Next j
Application.CutCopyMode = False

这是您要尝试的吗? HTH.

Is this what you're trying? HTH.

这篇关于在第一个空白行循环中复制和粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！