Excel 2003: macro to add data to next empty row

[Powerless user]

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

 

[Dave Peterson]

It overwrites any data when you run it a second, third, ... time.

If that's not what you want, then maybe you can pick out a column that can be
used to determine the next row to use.

Then you could replace this single line:

dRow = 1 'always starts at row 1

with this:

with destsheet
drow = .cells(.rows.count,"X").end(xlup).row + 1
end with

I used column X, but you can use any column that you want--as long as it's
filled when that row is used.

 

[Gary Keramidas]

and as some additional info to dave's post, i would always qualify the ranges.

use something like this so you always use the correct sheet. if you happen to
run the code and another sheet is active, it won't work.


For sRow = 1 To Range("B65536").End(xlUp).Row
'use pattern matching to find "9365" anywhere in cell
With Worksheets("Sheet1")
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
End With
Next sRow

 

[GadyC]

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.