CODEWITHSUNDEEP
excelvba
Hank
Hank

Reputation: 187

Pasting data in the last row

I am trying to paste data from sheet(template) to sheet(main) on the last used cell in column A.

But i cant seem to get the result i want. 

Dim lastrow As Long

If Range("a1").Value = "A" Then        
    Sheets("Template").Range("a1:e9").Copy
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Main").Range("a" & lastrow).paste        
End If

ERROR : Sheets("Main").Range("a" & lastrow).paste (object does not support this property or method)

Upvotes: 1

Views: 583

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

If you need to get only values of data then you can use variant.

Dim vDB as Variant
Dim rngT As Range
Dim Ws As Worksheet

    vDB = Sheets("Template").Range("a1:e9")
    Set Ws = Sheets("Main")
    With Ws
        If .Range("a1").Value = "A" Then
            Set rngT = .Range("a" & Rows.Count).End(xlUp)(2) ' it's lastrow +1
            rngT.Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
        End If
    End With

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

Try to fully qualify all your objects and variables (never assume or rely on ActiveSheet).

Also, the Copy >> Paste is a 1-line command (not 2). If you want to use 2-lines, then change the second one to PasteSpecial (even though it's not needed in your case).

If Sheets("Template").Range("A1").Value = "A" Then
    lastrow = Sheets("Main").Cells(Sheets("Main").Rows.Count, 1).End(xlUp).Row
    Sheets("Template").Range("A1:E9").Copy Destination:=Sheets("Main").Range("A" & lastrow + 1)
End If

Upvotes: 2

Related Questions