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

developer

excel

Macros and VBA

office 365

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

semiro1815

Brass Contributor

Feb 04, 2021

it says:

Run-time error '91': Object variable or With block variable not set

HansVogelaar

MVP

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

Forum Discussion

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

Resources