Automate wksht on cell change: formulas to values, copy / paste rows, delete sheet

I need assistance with a bit of worksheet automation. Excel 2003.

Thanks!

Quick summary of what currently works in my file:

Automated invoice creation from sheet “TEMPLATE”, including generation of unique / sequential Invoice Number.

Worksheets created from sheet “TEMPLATE” automatically named using cell “G4” (Invoice #).

Sheets “INDEX” and “InvNum” automatically update with information from invoices.

(Ignore sheets “TIME”, “Customer Data” and “TOTALS”)


** Functionality to add: **

First:

When a new row is added to sheet “INDEX” (when a new invoice is created), copy cell formulas from above or use VBA to enter cell formulas). Currently, I “dragged” the formulas down to row 500, but the total number needed on sheet “INDEX” is unknown so would be great for the formulas to be added as needed.

Second:

When a “PAID DATE” is entered on any invoice worksheet (sheet names according to Invoice Number) in cell “J4”, the PAID DATE enters on sheet “INDEX” automatically (this is working). Once the sheet “INDEX” is updated (column I), I want the following to happen:

1. Convert all formulas on the row that just received a “PAID DATE” to values. In other words, the code will need to make sure sheet “INDEX” column “D” matches the invoice number/sheet name that was just updated with a “PAID DATE”.
2. Copy this same row of values, delete row, shift cells up and PASTE this row to next open row on sheet “InvPd.”
3. Delete the invoice worksheet in which the "PAID DATE" was entered that triggered the above steps.
4. Repeat any time a “PAID DATE” is entered on any invoice.

CP Invoices - working 7.3.zip

Please let me know if you require additional information.

Hello, I have been busy solving my own problems. I only have one step left in my automation, and I am really stuck. From original post:

EXCEL 2003

First:

When a new row is added to sheet “INDEX” (when a new invoice is created), copy cell formulas from above or use VBA to enter cell formulas). Currently, I “dragged” the formulas down to row 500, but the total number needed on sheet “INDEX” is unknown so would be great for the formulas to be added as needed. - SOLVED (on my own!)

Second: STILL NEED ASSISTANCE

When a “PAID DATE” is entered on any invoice worksheet (sheet names according to Invoice Number) in cell “J4”, the PAID DATE enters on sheet “INDEX” automatically (this is working). After sheet “INDEX” is updated (column I), I want the following to happen (THIS IS UPDATED FROM THE ORIGINAL POST):

1. Convert all formulas on the row that just received a “PAID DATE” to values. In other words, the code will need to make sure sheet “INDEX” column D "INVOICE NUMBER” matches the invoice number/sheet name that was just updated with a “PAID DATE”.
2. MOVE the invoice worksheet in which the "PAID DATE" was entered that triggered the above steps TO AN EXISTING FILE.
4. Repeat any time a “PAID DATE” is entered on any CURRENT OR FUTURE invoice.


I was able to get the "move sheet to new file" working with this macro (triggered on worksheet change event):

Please Login or Register  to view this content.

HOWEVER, when I try to add in the first part (change formulas to values), I get stuck. I recorded a macro with all the steps needed, but I couldn't figure out how to convert the specific values of sheets and cells to be variables so they would work on all sheets.

Here is the recorded macro for all the steps:

Please Login or Register  to view this content.

I am having trouble uploading my file. I will try to attach.

Does anyone have some advice??

THANKS!