Macro to copy row

[kizzie37]

Hi I need a very quick macro that will copy the formulas from row 1 into row the next row down and then make the previous row, values only. I have a sheet that i will be pasting answers to a test into, some details form this sheet are referenced in sheet 2. so the macro will hard code the information then drop the formulas to the next row.



formulas in row 1 are:

Cell A2 - ='Answer Input Sheet'!$B$1
Cell B2 - ='Answer Input Sheet'!$F$34
Cell C2 - =IF('Answer Input Sheet'!$I$34>1<4,"Required if Job pertains","Refresher if requested")
Cell D2 - =IF('Answer Input Sheet'!$I$35>1<4,"Required if Job pertains","Refresher if requested")
Cell E2 - =IF('Answer Input Sheet'!$I$36>=4,"Required if Job pertains","Refresher if requested")

I will be be adding up to 50 test results (one at a time) so the process needs to keep repeating, can anyone help please?

 

[Smitty]

See if this gets you started:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">With</SPAN> ActiveCell<br>        <SPAN style="color:#00007F">With</SPAN> .EntireRow<br>            .Copy ActiveCell.Offset(1)<br>            .Value = .Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[BrandonWLH]

Sub asd()
   Dim rng As Range
   Set rng = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)
   Range(rng, rng.Offset(0, 4)).Copy
   rng.Offset(1, 0).PasteSpecial xlPasteAll
   rng.Offset(0, 0).PasteSpecial xlPasteValues
End Sub

Tested, works. Leaves values in the previous row, moves formulas to the next row. Works 1 at a time. Just assign this to a button or something.

 

[kizzie37]

awsome guys! thanks for your help.