0

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Amit Paul
  • 1
  • 2
  • Row counting variables must be of type `Long` because Excel has more rows than `Integer` can handle. Actually there is [no benefit in using `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) in VBA and it can generally replaced by `Long`. – Pᴇʜ Sep 30 '20 at 08:30

2 Answers2

1

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
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
1

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

Wasif
  • 14,755
  • 3
  • 14
  • 34
  • In addition to that, row counting variables must be of type `Long` because Excel has more rows than `Integer` can handle. Actually there is [no benefit in using `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) in VBA and it can generally replaced by `Long`. – Pᴇʜ Sep 30 '20 at 08:32