i would use .userrange to determine the last row in use.

dRow = destSheet.usedrange.rows.count + 1

then you do not inc(dRow) for each iteration but seek directly the 1st free
row.


"Powerless user" wrote:

When I run this macro with new data it overwrites instead of pasting to the
next empty row in the destination sheet. Any suggestions?

Sub Copy9365()
'Copy cells of cols A,D,F,H,J,L,M,N from rows containing "9365" in
'col B of the active worksheet (source sheet) to cols
',A-H of Tester (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("tester")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1

For sRow = 1 To Range("B65536").End(xlUp).Row
'use pattern matching to find "9365" anywhere in cell
If Cells(sRow, "B") Like "*9365*" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,D,F,H,J,L,M & N
Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
Cells(sRow, "d").Copy Destination:=DestSheet.Cells(dRow, "B")
Cells(sRow, "f").Copy Destination:=DestSheet.Cells(dRow, "C")
Cells(sRow, "h").Copy Destination:=DestSheet.Cells(dRow, "D")
Cells(sRow, "J").Copy Destination:=DestSheet.Cells(dRow, "E")
Cells(sRow, "L").Copy Destination:=DestSheet.Cells(dRow, "F")
Cells(sRow, "M").Copy Destination:=DestSheet.Cells(dRow, "G")
Cells(sRow, "N").Copy Destination:=DestSheet.Cells(dRow, "H")

End If
Next sRow
Sheets("tester").Select

End Sub