Forum Discussion

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...

Feb 04, 2021

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

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

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...

HansVogelaar

MVP

Feb 04, 2021

semiro1815

By AB, did you mean the 28th column, or did you mean columns A and B?

semiro1815

Brass Contributor

Feb 04, 2021

HansVogelaar i meant column A and B. Sorry if was not very clear. And Column C and D as well

HansVogelaar

MVP

Feb 04, 2021

semiro1815

Change the macro to

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