How do I write this Microsoft Excel macro?

5 pts.
Tags:
Microsoft Excel
Microsoft Excel macros
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.
ASKED: March 24, 2009  9:56 PM
UPDATED: March 25, 2009  2:33 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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: ‘

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following