Forum Discussion

semiro1815's avatar
semiro1815
Brass Contributor
Feb 04, 2021
Solved

COUNT & Copy to Last Row, Paste to another sheet to Last Row (FIRST EMPTY ROW, adding, not replacing

Hey friends. Im New in VBA. Could you help me at this?

 

So, we have 2 columns: AB with data, in Sheet1
I want to COPY until Last Row in AB Column from Sheet1
and paste it to Sheet2, column CD in FIRST EMPTY ROW (i mean by adding more data, cause CD is supposed to have previous data)

Can anyone help? It would be a huge favour for me! THANKS !!

developer
excel
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Feb 04, 2021

    semiro1815 

    That happens if columns C and D are empty. See if this is better:

    Sub CopyData()
    Dim m As Long
    Dim r As Long
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error Resume Next
    r = Worksheets("Sheet2").Range("C:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    If Err Then
        r = 1
    End If
    On Error GoTo 0
    Worksheets("Sheet1").Range("A1:B" & m).Copy Destination:=Worksheets("Sheet2").Range("C" & r)
    Application.CutCopyMode = False
End Sub

64 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 04, 2021

    semiro1815 

    Try this:

    Sub CopyData()
    Dim m As Long
    Dim r As Long
    m = Worksheets("Sheet1").Range("AB" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
    r = Worksheets("Sheet2").Range("CD" & Worksheets("Sheet2").Rows.Count).End(xlUp).Row + 1
    Worksheets("Sheet1").Range("AB1:AB" & m).Copy Destination:=Worksheets("Sheet2").Range("CD" & r)
    Application.CutCopyMode = False
End Sub
    • semiro1815's avatar
      semiro1815
      Brass Contributor
      Feb 04, 2021

      HansVogelaar 

      My friend, actually it does nothing, it runs without error, but the data are not shown in CD in Sheet2. They arent pasted anywhere. I don't know where the problem is...

Resources