vba 如何粘贴到下一个空单元格而不是粘贴到同一单元格

How to paste at the next empty cell instead of pasting at the same cell

提问by user2766881

Im having a problem when pasting data at the recompile sheet. The recompile sheet is like a summary sheet and will paste all the data from the selected worksheet that is specify. The master sheet is a reference sheet where range ("AA") consist of all the worksheet name that i want to copy and paste to recompile sheet. Example of the worksheet name is worksheet A,B,C...etc I just need to write the worksheet name at master sheet range ("AA") and the program will track down all the worksheet name that have been specified. My problem is when i paste data from worksheet A data from worksheet B,C,D will also be pasted at the same cell causing it to be overwrite. What i am trying to do is to paste data worksheet A to recompile sheet then the next empty cell is data from worksheet B and so on. This is my code.

在重新编译表上粘贴数据时遇到问题。重新编译表就像一个汇总表，将从指定的选定工作表中粘贴所有数据。主表是一个参考表，其中范围（“AA”）包含我想要复制和粘贴以重新编译表的所有工作表名称。工作表名称的示例是工作表 A、B、C...等我只需要在主表范围（“AA”）中写入工作表名称，程序将跟踪所有已指定的工作表名称。我的问题是，当我粘贴工作表 A 中的数据时，工作表 B、C、D 中的数据也将粘贴在同一单元格中，导致其被覆盖。我想要做的是粘贴数据工作表 A 以重新编译工作表，然后下一个空单元格是工作表 B 中的数据，依此类推。这是我的代码。

Private Sub CommandButton2_Click()

 Dim Sheetname, myrange As String
 Dim A, noOfrows As Integer
 Dim startRow As Integer

For i = 2 To Worksheets("Master Sheet").Cells.SpecialCells(xlCellTypeLastCell).Row

Sheetname = Worksheets("Master Sheet").Cells(i, 27).Value'Reference sheet range ("AA")
 noOfrows = Worksheets(Sheetname).Cells.SpecialCells(xlCellTypeLastCell).Row
 myrange = "A2:N" & CStr(noOfrows)'Data from column A to column N
 Worksheets(Sheetname).Select
 Worksheets(Sheetname).Range(myrange).Select
 Selection.Copy
 Sheets("Recompile").Select         
 Range("A2").Select
 ActiveSheet.Paste

 Next i
 End Sub

采纳答案by Jerome Montino

There are a couple of things that are not in your code. Seeing that you're approaching this process using an iteration/loop, some confusion is only expected. My main concerns though are that you use ActiveSheetand Selectand that your final actions refer to only one cell (Range("A2"). This means the resultant action of each iteration changes just this cell. See below for one possible approach to this.

有几件事不在您的代码中。看到您正在使用迭代/循环来处理这个过程，只会出现一些混乱。不过，我主要担心的是您使用ActiveSheet和Select并且您的最终操作仅涉及一个单元格 ( Range("A2")。这意味着每次迭代的结果动作只改变这个单元格。请参阅下面的一种可能的方法。

First, you need to iterate over the names of the worksheets found in Master Sheet, Column AA. Next, you have to check for the last row of the worksheet that corresponds to this name. Based on your code, the range of cells from A2to the last row of Column Nis copied to the Recompile sheet, starting with Cell A2(I'm assuming you have headers).

首先，您需要遍历在Master Sheet, Column AA. 接下来，您必须检查与此名称对应的工作表的最后一行。根据您的代码，从单元格A2到最后一行的单元格范围Column N被复制到重新编译表，从单元格开始A2（我假设您有标题）。

However, after every copy, you need to do three things as well: 1. Move to the next name in your list of sheet names. 2. Find the new last row of the sheet corresponding to the next name. 3. Find the new last row of the Recompile sheet, so you can start there.

但是，在每次复制之后，您还需要做三件事： 1. 移到工作表名称列表中的下一个名称。2. 找到对应于下一个名称的工作表新的最后一行。3. 找到重新编译表的新最后一行，以便您可以从那里开始。

Now, you need this to loop as many times as there are sheets. The logic of this gives rise to the following code (untested, please do in a backup copy of your workbook):

现在，您需要它循环与工作表一样多的次数。其逻辑产生以下代码（未经测试，请在工作簿的备份副本中执行）：

Private Sub Boom()

    Dim ShMaster As Worksheet, ShRecompile As Worksheet
    Dim ShName As String, RngToCopy As Range
    Dim LRowSrc As Long, LRowRecompile As Long 'Src is other sheets
    Dim LRowMaster As Long 'This will be our iteration limit.
    Dim Iter As Long

    With ThisWorkbook
        Set ShMaster = .Sheets("Master Sheet")
        Set ShRecompile = .Sheets("Recompile")
    End With

    LRowMaster = ShMaster.Cells(Rows.Count, 27).End(xlUp).Row

    For Iter = 2 to LRowMaster
        ShName = ShMaster.Cells(Iter, 27).Value
        With ThisWorkbook.Sheets(ShName)
            LRowSrc = .Cells(Rows.Count, 1).End(xlUp).Row
            Set RngToCopy = .Range("A2:N" & LRowSrc)
            RngToCopy.Copy
        End With
        With ShRecompile
            LRowRecompile = .Cells(Rows.Count, 1).End(xlUp).Row + 1
            .Range("A" & LRowRecompile).PasteSpecial xlPasteAll
        End With
    Next Iter

End Sub

One major assumption is made here: that your list of sheet names start in AA1. This is important. If it doesn't, change Iter = 1to Iter = nwhere nis the row number of the true starting cell of your list.

这里做出了一个主要假设：您的工作表名称列表以 AA1 开头。这个很重要。如果没有，更改Iter = 1到Iter = n哪里n是你的列表中真正启动细胞的行数。

Let us know if this helps.

如果这有帮助，请告诉我们。

UPDATE:As the list starts in AA2, I have updated the above code.

更新：随着列表的开始AA2，我更新了上面的代码。

回答by sam092

Not tested

未测试

Change this part

改变这部分

Sheets("Recompile").Select         
Range("A2").Select
ActiveSheet.Paste

to

到

With Sheets("Recompile")
   .Range("A" & .Rows.Count).End(xlUp).offset(1,0).Paste
End With