How do I write this Microsoft Excel macro?
Hi, I'm trying to create a macro that will move rows in my spread sheet. I am working with orders that are keyed into excel, once the orders ship or are canceled; the rows are highlighted green or red. I want these shipped/canceled (green/red) rows to cut from the first page and paste into another sheet automatically when the color is changed. If you can answer this question, would you please send me an email at js713@live.com
Thank you for your help.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 24, 2009 9:56 PM
UPDATED:
March 25, 2009 2:33 PM
Answer Wiki:
If your current orders are on Sheet1 and you want to move them to Sheet2 your macro would be something like the following if the order was in Column A and the first Blank in Column A was the
end of the data. You may have some tweeding on the color numbers to look for but this should be a good start.
for i=1 to 999999
if sheets("Sheet1").range("A" & format(i,"000000") ="" then goto AllDone
if sheets("Sheet1").range("A" & format(i,"000000").backcolor =rgb(255,0,0) or sheets("Sheet1").range("A" & format(i,"000000").backcolor =rgb(0,255,0) then ' we have an order to move
' now find first blank row in Sheet2
for j=1 to 999999
if sheets("Sheet2").range("A" & format(i,"000000") ="" then ' found the insertion point
' the following line will select the current line columns A thru Z. You can select any columns you want
sheets("Sheet1").range("A" & format(i,"000000" & "Z" & format(i,"000000" ).select
' the following line copys the line to the clickboard
sheets("Sheet1").range("A" & format(i,"000000" & "Z" & format(i,"000000" ).copy
' the following line pastes the line to Sheet2 from the clickboard
sheets("Sheet2").range("A" & format(j,"000000" & "Z" & format(j,"000000" ).paste
' the following line deletes the line from Sheet1
sheets("Sheet1").range("A" & format(i,"000000" & "Z" & format(i,"000000" ).delete
goto CopiedIt
end if
next j
end if
CopiedIt:'
next i
AllDone: '
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
