excel - 最后一行不同的工作表和复制粘贴

Question

我知道“最后一行”问题已经出现了好几次，但即使查看现有线程，我也找不到正在发生的事情。这是我第一次编写宏，所以我只能到达某个点，我粘贴代码并稍后提出问题：

Option Explicit

Sub Practice()

'Last Row Searcher

Dim Sht As Worksheet
Set Sht = ActiveSheet
Dim Last_Row As Long
With Sht
    Last_Row = .Range("A9999").End(xlUp).Row
End With

'Column A to D
Sheet9.Select
Range("A2:A" & Last_Row).Copy
Sheet11.Select
Range("D" & Last_Row).Select
ActiveSheet.Paste

'Column C to F
Sheet9.Select
Range("C2:C" & Last_Row).Copy
Sheet11.Select
Range("F" & Last_Row + 1).Select
ActiveSheet.Paste

'Column E to G
Sheet9.Select
Range("E2:E" & Last_Row).Copy
Sheet11.Select
Range("G" & Last_Row + 1).Select
ActiveSheet.Paste

'Column I to L
Sheet9.Select
Range("I2:I" & Last_Row).Copy
Sheet11.Select
Range("L" & Last_Row + 1).Select
ActiveSheet.Paste

End Sub

问题一：

当我将复制的内容粘贴到另一个工作表时，它会直接将前一个工作表中的“Last_Row”中的内容粘贴到“Last_Row”中，而不是寻找活动工作表的新“Last_Row”。有没有解决的办法？

问题2

我多次重复相同的代码但使用不同的列，因为它们不在一起我将列 A 复制到 D，然后将 C 复制到 F，等等。

它对我有用，但出于好奇，有没有办法一次完成所有工作？

标签： excelvba

Answer 1

（之后的第一个空行）最后一个非空行

Option Explicit

Sub Practice()

'Last Row Searcher

    Const frSrc As Long = 2                 ' Source First Row
    Const strSrc As String = "A,C, E, I"    ' Source Column Letters
    Const strTgt As String = "D, F,G, L"    ' Target Column Letters

    Dim wsSrc As Worksheet  ' Source Worksheet
    Dim wsTgt As Worksheet  ' Target Worksheet
    Dim rngSrc As Range     ' Source Column Range
    Dim rngTgt As Range     ' Target Column Range
    Dim vntS As Variant     ' Source Column Array
    Dim vntT As Variant     ' Target Column Array
    Dim lrSrc As Long       ' Source Last Non-Empty Row
    Dim frTgt As Long       ' Target First Row After Last Non-Empty Row
    Dim i As Long           ' Source and Target Array Elements Counter
    Dim colSrc As String    ' Source Column Letter
    Dim colTgt As String    ' Target Column Letter

    ' Beware, you are using CodeNames, which are not the names on the TAB.
    Set wsSrc = Sheet9
    Set wsTgt = Sheet11

    ' Populate Column Arrays (vntS, vntT).
    vntS = Split(strSrc, ","): vntT = Split(strTgt, ",")

    ' Loop through elements of Source (or Target) Column Array.
    For i = 0 To UBound(vntS)
        ' Calculate Column Letter (colSrc, colTgt)
        colSrc = Trim(vntS(i)): colTgt = Trim(vntT(i))
        ' Calculate Source Last Non-Empty Row.
        lrSrc = wsSrc.Range(colSrc & wsSrc.Rows.Count).End(xlUp).Row
        ' Calculate Target First Row After Last Non-Empty Row.
        frTgt = wsTgt.Range(colTgt & wsTgt.Rows.Count).End(xlUp).Row + 1
        ' Calculate Source Column Range.
        Set rngSrc = wsSrc.Range(colSrc & frSrc & ":" & colSrc & lrSrc)
        ' Calculate Target Column Range.
        Set rngTgt = wsTgt.Range(colTgt & frTgt).Resize(rngSrc.Rows.Count)
        ' Write values of Source Column Range to Target Column Range.
        rngTgt.Value = rngSrc.Value
    Next

End Sub

编辑：

Sub Practice2()

'Last Row Searcher

    Const frSrc As Long = 2                 ' Source First Row
    Const strSrc As String = "A,C, E, I"    ' Source Column Letters
    Const strTgT As String = "D, F,G, L"    ' Target Column Letters

    Dim wsSrc As Worksheet  ' Source Worksheet
    Dim wsTgt As Worksheet  ' Target Worksheet
    Dim rngSrc As Range     ' Source Column Range
    Dim rngTgt As Range     ' Target Column Range
    Dim vntS As Variant     ' Source Column Array
    Dim vntT As Variant     ' Target Column Array
    Dim lrSrc As Long       ' Source Last Non-Empty Row
    Dim frTgt As Long       ' Target First Row After Last Non-Empty Row
    Dim i As Long           ' Source and Target Array Elements Counter
    Dim colSrc As String    ' Source Column Letter
    Dim colTgt As String    ' Target Column Letter

    ' Beware, you are using CodeNames, which are not the names on the TAB.
    Set wsSrc = Sheet9
    Set wsTgt = Sheet11

    ' Populate Column Arrays (vntS, vntT).
    vntS = Split(strSrc, ",")
    vntT = Split(strTgT, ",")

    ' Calculate Target First Row After Last Non-Empty Row.
    frTgt = wsTgt.Range(Trim(vntT(0)) & wsTgt.Rows.Count).End(xlUp).Row + 1

    ' Loop through elements of Source (or Target) Column Array.
    For i = 0 To UBound(vntS)
        ' Calculate Column Letter (colSrc, colTgt)
        colSrc = Trim(vntS(i)): colTgt = Trim(vntT(i))
        ' Calculate Source Last Non-Empty Row.
        lrSrc = wsSrc.Range(colSrc & wsSrc.Rows.Count).End(xlUp).Row
        ' Calculate Source Column Range.
        Set rngSrc = wsSrc.Range(colSrc & frSrc & ":" & colSrc & lrSrc)
        ' Calculate Target Column Range.
        Set rngTgt = wsTgt.Range(colTgt & frTgt).Resize(rngSrc.Rows.Count)
        ' Write values of Source Column Range to Target Column Range.
        rngTgt.Value = rngSrc.Value
    Next

End Sub