Copy Formulas Down Just One Row

[C.R.]

Hello,

I am stuck today.

In E12:J12 I have some formulas that pull data from an order info sheet.
Each time I get a new order, I’d like to copy the formulas in E12:J12 down just one row to E13:J13. Then…PasteSpecial>>> Values in E12:J12.

Next time there’s a new order, E13:J13 formulas would copy down to E14:J14.
Then PasteSpecial>>> Values in E13:J13.

This will go on as more orders come in…
I have many sheets like this with other products on them.

Thanks in advance for any help on this.

Spreadsheet is attached.

C.R.

[SamT]

Try this. I moved the Worksheet_Change to the order Info sheet, because it looked like that sheet was changed manually. I put very versatile LastRow and NextRow functions is a new module "Globals.

Only tested in XL2002, but every time I change a cell in OrderInfo, bottom of Col A, it works.

[C.R.]

SamT,

Many thanks for this. As you could see... I was floundering yesterday.
I will play around with this when I get to the office.

C.R.

[snb]

 
Sub M_snb()
    range("E13:J13")=range("E12:J12").formula
    range("E12:J12").value=range("E12:J12").value
end sub

[C.R.]

SamT,

Your solution works so I am marking this solved. I need to expand it to my others products I'm tracking.

snb: For future reference, how does your solution Continue on down row by row. Will it not just continue to redo E12:J12. / E13:J13 ? I have't tested this yet so I am just asking at this point.

Thank you both very much for your help.

C.R.

[snb]

I only showed you a method to 'copy' formulae and to revert formulae to values.
You may adapt the code.