CODEWITHSUNDEEP
excelvba
Amit Paul
Amit Paul

Reputation: 1

Copy, paste then move next row

I am trying to copy the data from sheet "MAINSHEET" cell M9 to O9 and paste it to sheet "COPYDATA", column B. First it will paste the data to B2 and next it will B3, next B4 and will continue... But it is not going below, keep pasting on B2 cell.

Sub Copy()

Dim lastrow As Integer
lastrow = ThisWorkbook.Sheets("COPYDATA").Cells(Rows.Count, 1).End(xlUp).Row
    
Sheets("MAINSHEET").Range("M9:O9").Copy
Worksheets("COPYDATA").Range("B" & lastrow + 1).PasteSpecial xlPasteValues

End Sub

Upvotes: 0

Views: 675

Answers (2)

wasif
wasif

Reputation: 15480

Try this:

Sub Copy()

Dim lastrow As Long
lastrow = ThisWorkbook.Sheets("COPYDATA").Cells(ThisWprkbook.Sheets("COPYDATA").Rows.Count, "B").End(xlUp).Row


Sheets("MAINSHEET").Range("M9:O9").Copy
Worksheets("COPYDATA").Range("B" & lastrow + 1).PasteSpecial xlPasteValues

End Sub

You need to modify the code to get row count of the COPYDATA sheet and use the column "B".

Upvotes: 1

Davesexcel
Davesexcel

Reputation: 6984

You are counting the rows in column 1 but pasting in column 2, so the lastrow always stays the same.

Count the rows in column B instead.

lastrow = ThisWorkbook.Sheets("COPYDATA").Cells(Rows.Count, 2).End(xlUp).Row

Upvotes: 1

Related Questions