Excel 2003: macro to add data to next empty row
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 |
Excel 2003: macro to add data to next empty row
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. 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 -- Dave Peterson |
Excel 2003: macro to add data to next empty row
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 |
All times are GMT +1. The time now is 03:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com