SOLVED

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

Brass Contributor

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

64 Replies

@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

@Hans Vogelaar 

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

@semiro1815 

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

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

@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

@Hans Vogelaar  it shows an error my friend at this line:

r = Worksheets("Sheet2").Range("C:D"). . . 

 

I dont know, can you fix it?

 

it says:

Run-time error '91': Object variable or With block variable not set
best response confirmed by semiro1815 (Brass Contributor)
Solution

@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

@Hans Vogelaar  You are simply the BEST my friend ! You just saved my life. 1 billion thanks. Much love from Albania <3

@Hans Vogelaar  Hey friend, one more question, how can i modify that code, so if column A & B are empty, not to show an error?

@semiro1815 

Like this:

Sub CopyData()
    Dim m As Long
    Dim r As Long
    On Error Resume Next
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err Then
        MsgBox "There are no data to copy!", vbExclamation
        Err.Clear
        Exit Sub
    End If
    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

removed reply

@semiro1815 

That's hard to tell.

I'd single-step through the code and inspect the values of m10, r10, m11, r11 etc. to see if one of those has an unexpectedly large value.

Hmm, do you think it may be there, at m11, r11 etc? Cause it counts, and copy and paste the cells that have data, for example 10cells. so it doesnt have to paste until 1,000,000 row. im worried it may be somewhwere else. But u know best. So we cant do anything about it?

@semiro1815 

When you single-step through the code by pressing F8 repeatedly, you can view the value of variables such as m11 by hovering the mouse pointer over them.

Perhaps you suddenly see a very large value. That would provide a clue...

@Hans Vogelaar  So we have our code here. How can we modify this that: we have a text in Sheet1 cell C1, to be pasted in Sheet2 Column E, that times how long is column C and D that we pasted with data. like pasting drop-down.

 

 

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

 

 

@Hans Vogelaar 

visually:

column __A__B__C___ ...Sheet1

row1_____w__p__k__

row2_____w__p_____

row3_____w__p_____

..............................................

column__A__B__C__D__E Sheet2

row1____________w__p__k

row2____________w__p__k

row3____________w__p__k

 

(single step helped. i found it. and i have to do this, in order to remove that large data. Can you help please?)

@Hans Vogelaar Hmm ok, think of this:

 

We are in Sheet1 and we have data: in Column A and Column B and data in C1

what i need:

Count, Copy, Paste Column A and Column B from Sheet1  - to Sheet2 to Last Row in Column A and Column B

and

C1 from Sheet1 to be pasted to Column C Sheet2, and to be multiplied/drop-down-cell/that long, that column B has data.

 

Here is a photo:

@semiro1815 

You changed the question slightly: instead of pasting to columns C and D on Sheet2, you now want to paste to columns A and B.

Sub CopyData()
    Dim m As Long
    Dim r As Long
    On Error Resume Next
    m = Worksheets("Sheet1").Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Err Then
        MsgBox "No data to copy!", vbExclamation
        Exit Sub
    End If
    r = Worksheets("Sheet2").Range("A:B").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("A" & r)
    Worksheets("Sheet1").Range("C1").Copy Destination:=Worksheets("Sheet2").Range("C" & r).Resize(m)
    Application.CutCopyMode = False
End Sub
1 best response

Accepted Solutions
best response confirmed by semiro1815 (Brass Contributor)
Solution

@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

View solution in original post