Paste value and Auto row increment

[AN]

I have to sheets named "Data" and "Test". Data sheet contains everyday
sales amount in row A22. 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 A22. Next time when I run the
macro, it should copy the value from A33 and so on.

could anyone help!
Thanks
AN

 

[Guest]

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