I have an Excel worksheet that tracks gift certificate sales and redemptions. Sometimes the amount redeemed (column Q) is less than the total available (column H). Redemption amount currently turns red to warn staff that there is credit, but they then manually change sheet. How do I write a macro/trigger an event to insert a row below the active cell in column Q to copy the original row data to the new row, change values for (Hnew) to (Horiginal - Moriginal) and then Horiginal to Moriginal. I’m stumped because the row is variable due to the list being in alphabetical order.

  • Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?. – DavidPostill Jan 7 '17 at 15:17
  • If you don’t know VBA, learn it (there are lots of resources on the Internet, in Excel itself, and in Super User) and try to solve the problem yourself.  See How do I add VBA in MS Office? for general information.  A good way to start is to click “Record Macro” on the “Developer” tab, walk through the actions that you want to automate, and then click “Stop Recording” (which is the same button as “Record Macro”).  Your keystrokes are translated into the equivalent VBA.  If you get something working, but you run into trouble, then you can come back to us with specifics. – Scott Jan 8 '17 at 2:15

Based on your main problem, you can use the following line, to insert a row below the active cell.

'Insert row below active cell ActiveCell.Offset(1).EntireRow.Insert

Unfortunately I don't get the purpose of the copy, paste routine you tried to explain. But with the Copy Range command I think you can make your way:

ActiveWorksheet.Range("A1:K" & your_variable_end_of_copy_area).Copy ActiveWorksheet.Range("your_variable_destination_to_insert")

If you paste the code you already have, I could help you in a better way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.