Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining row position in a loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining row position in a loop
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 -- Michael J. Malinsky "Tom" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining row position in a loop
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 "Tom" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining Formulas | New Users to Excel | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Maintaining Formulas | Excel Discussion (Misc queries) | |||
Maintaining Row Number | Excel Discussion (Misc queries) | |||
Maintaining formats of files | Excel Discussion (Misc queries) |