vba - 将数据复制并粘贴到所选行

Question

我的工作代码中有一段代码，它将其他工作表中的数据复制并粘贴到一个主工作簿主工作表中。下面的代码允许我将 BX 列中的数据复制并粘贴到 A 列的第一个空行，并将 CC 列复制并粘贴到 B 列的第一个空行。但是，我想将 CC 列粘贴到 B 列的(第 10 行)。我怎样才能做到这一点？

 lRow = copySheet.Cells(copySheet.Rows.Count, 1).End(xlUp).Row

 With copySheet.Range("BX2:BX" & lRow)
   pasteSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
   .Resize(.Rows.Count, .Columns.Count) = .Value
 End With

 'Determine last row of Column B in copySheet
 lRow = copySheet.Cells(copySheet.Rows.Count, 1).End(xlUp).Row

 With copySheet.Range("CC2:CC" & lRow)
  pasteSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
  .Resize(.Rows.Count, .Columns.Count) = .Value
 End With

你能告诉我如何确定选择要复制的行数吗？

编辑:现在我想添加一个 if另一列的条件，应该说:

if

column U in Worksheet "data" has cell value "8636" then these values should be pasted to Column H in Worksheet "KomKo"(pastesheet); to the next row as I used the code above in the "with" part.

否则(如果 H 列中的值不是 8636)那么它应该粘贴

value inside this column to Column G at Worksheet "KomKo"(pastesheet) with same preferences as above again

.

我怎样才能做到这一点 ？

Answer 1

更改pasteSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value到那个pasteSheet.Range("B10").Resize(.Rows.Count, .Columns.Count) = .Value
*************回答问题编辑****************
******* 添加了 maxR - H 和 G 列的最后一行最高 *******
你可以做这样的事情来得到你需要的东西:

Sub check8636values()

    Dim copySheet, pasteSheet As Worksheet
    Dim lRowU, lRowH, lRowG, maxR, i As Long

    'Dont forget to change to the correct sheet names!!!!
    Set copySheet = ThisWorkbook.Sheets("data")
    Set pasteSheet = ThisWorkbook.Sheets("KomKo")

    lRowU = copySheet.Cells(copySheet.Rows.Count, "U").End(xlUp).Row

    For i = 1 To lRowU

        lRowG = pasteSheet.Cells(pasteSheet.Rows.Count, "G").End(xlUp).Row + 1
        lRowH = pasteSheet.Cells(pasteSheet.Rows.Count, "H").End(xlUp).Row + 1
        maxR = Application.Max(lRowG,lRowH)

        If copySheet.Cells(i, "U").Value = "8636" Then

            pasteSheet.Cells(maxR, "H").Value = copySheet.Cells(i, "U").Value
            pasteSheet.Cells(maxR, "Y").Value = copySheet.Cells(i, "T").Value

        Else

            pasteSheet.Cells(maxR, "G").Value = copySheet.Cells(i, "U").Value
            pasteSheet.Cells(maxR, "X").Value = copySheet.Cells(i, "T").Value

        End If

    Next i

End Sub