Maintaining row position in a loop

[Tom]

Hi,

I have set of cells that can be any number of rows, but
let's say it's 100 rows (today) x 4 columns. If the value
in the first cell e.g. A1 = "55", I want to copy the
entire row to sheet "55", after the last row in
sheet "55". To do this I need to go to sheet "55" and find
the first blank row, prior to doing the paste. I can do
all this. However, when I return to the source sheet
containing the 100 rows, I want to be in my loop and
continue to the next row. If the value is "55", I'll
repeat the above. If the value is "65" I'll copy that row
to sheet "65". My problem is, that when I return to my
source (the 100 rows), Excel has lost it's position.

Thanks,

Tom

 

[Michael J. Malinsky]

Sub CopyPasteRow()

rw=1
While Range("A" & rw").Value <> ""
'Insert your copy and paste routine here
rw=rw+1
Wend

End Sub

This goes through each cell in column A and, if the cell is not blank, then
it will execute your copy and paste routine. After the copy and paste, the
row number is increased by 1 and it keeps going until a blank cell in column
A is hit.

HTH

 

[Tom Ogilvy]

You don't need to go anywhere

Sub CopyRowsFromActivesheet()
Dim cell as Range
for each cell in Range(Range("A1"),Cells(rows.count,1).End(xlup))
cell.EntireRow.Copy Destination:=Worksheets(Cell.Value). _
Cells(rows.count,1).End(xlup)(2)
Next
End Sub




Regards,
Tom Ogilvy