PDA

View Full Version : Solved: Copy Formulas Down Just One Row



C.R.
03-19-2013, 10:45 AM
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
03-19-2013, 05:07 PM
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.
03-20-2013, 07:37 AM
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
03-20-2013, 08:18 AM
Sub M_snb()
range("E13:J13")=range("E12:J12").formula
range("E12:J12").value=range("E12:J12").value
end sub

C.R.
03-21-2013, 08:17 AM
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
03-21-2013, 10:21 AM
I only showed you a method to 'copy' formulae and to revert formulae to values.
You may adapt the code.