Paste value and Auto row increment
 

I have to sheets named "Data" and "Test". Data sheet contains everyday
sales amount in row A2:D2. eg.
Data Sheet
Date Agency Dealer Corporate
1/09/2007 545,454.00 15,022.00 45,210.00

Test Sheet
Date Agency Dealer Corporate
1/09/2007 545,454.00 15,022.00 45,210.00

This information gets overwritten everyday. Test sheet is linked to
data sheet and contains the same data. But test sheet will keep a
record of sales history not only today's data. Everyday before
overwriting the amount for 'data' sheet, I want to run a macro which
will copy the formula to the next row (which is link to data sheet)
and then copy and paste value to A2:D2. Next time when I run the
macro, it should copy the value from A3:D3 and so on.

could anyone help!
Thanks
AN

Paste value and Auto row increment
 

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/22/2007 by Joel
'

'
With Sheets("Test")
.Rows("2:2").Insert
.Rows("3:3").Copy
.Rows("2:2").PasteSpecial _
Paste:=xlPasteValues
End With
End Sub


"AN" wrote:

I have to sheets named "Data" and "Test". Data sheet contains everyday
sales amount in row A2:D2. eg.
Data Sheet
Date Agency Dealer Corporate
1/09/2007 545,454.00 15,022.00 45,210.00

Test Sheet
Date Agency Dealer Corporate
1/09/2007 545,454.00 15,022.00 45,210.00

This information gets overwritten everyday. Test sheet is linked to
data sheet and contains the same data. But test sheet will keep a
record of sales history not only today's data. Everyday before
overwriting the amount for 'data' sheet, I want to run a macro which
will copy the formula to the next row (which is link to data sheet)
and then copy and paste value to A2:D2. Next time when I run the
macro, it should copy the value from A3:D3 and so on.

could anyone help!
Thanks
AN