0
Private Sub CommandButton21_Click()

a = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If Worksheets("Sheet1").Cells(i, 3).Value = "shipment oi" Then

    Worksheets("Sheet1").Rows(i).Copy
    Worksheets("Sheet2").Activate
    b = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet2").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Sheet1").Activate

    End If
Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

End Sub

Code manages to reflect from A2 onwards

How do I make it reflect in D2 instead?

braX
  • 11,506
  • 5
  • 20
  • 33
Rahman
  • 1
  • 2
  • `Worksheets("Sheet2").Cells(b + 1, 4).Select` – braX Nov 07 '21 at 02:49
  • `Worksheets("Sheet2").Cells(b + 1, 4).Select`. The 2nd argument of `Cells` indicates the column number of the cell so 4 is column D. Also you will benefit from reading on [how to avoid using select/activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) – Raymond Wu Nov 07 '21 at 02:53
  • @braX Thank you for your input! but im now facing an error that reflects Run time error '1004': Application-defined or object defined error. any idea on this? much appreciated for your help. I'm a complete novice in VBA. – Rahman Nov 07 '21 at 03:14
  • @RaymondWu Thank you for your input! but im now facing an error that reflects Run time error '1004': Application-defined or object defined error. any idea on this? much appreciated for your help. I'm a complete novice in VBA. – Rahman Nov 07 '21 at 03:14
  • Which line did the error occur? @Rahman – Raymond Wu Nov 07 '21 at 03:41
  • Since you are now pasting to column D, you might want to get the last row of column D instead so `b = Worksheets("sheet2").Cells(Worksheets("sheet2").Rows.Count, 4).End(xlUp).Row` – Raymond Wu Nov 07 '21 at 04:29

0 Answers0