Enter and clear values but retain records somewhere else

[seang87]

Hi All,

I have been putting together a general journal style spreedsheet and I'm not sure if Excel can accommodate for this:

I need users to enter values in sheet 1 i.e (A6:R99), then theses values to be automatically be stored in sheet 2 (A6:R99), now here's the issues, from this point the user will clear the original sheet 1 range (A6:R99) and enter a new set of values. BUT I need sheet 2 to retain the original values.
Then new data entered into sheet 1 (still at A6:R99) should then be stored in sheet 2 at the next interval (A100:R199) if possible anywhere will do but the important thing being a continues record of values entered.

i.e

Step 1 (user enters values sheet 1)
Sheet 1
Cost center Account Value
Sales 100-200 $500


Sheet 2
Cost center Account Value
Sales 100-200 $500

Step 2 (user clears SHEET 1)

Step 3 ( user enters new values sheet 1)
Sheet 1
Cost center Account Value
Admin 200-200 $1500



Sheet 2
Cost center Account Value
Sales 100-200 $500
Admin 200-200 $1500



I may be asking a lot but any help would be most appreciated.

 

[sastoka]

Hi There,

Assign that code to a button.

Sub Copy_1_To_2()

    Dim LastRow1 As Long
    Dim LastRow As Long
    Dim LastCol As Long
    Dim sh As Worksheet
    
    Set sh = Sheets("Sheet1")
    
    LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    LastRow1 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    
    
    Range(sh.Cells(1, 1), sh.Cells(LastRow, LastCol)).Copy Destination:=Sheets("Sheet2").Range("A" & LastRow1 + 1)
    Range(sh.Cells(1, 1), sh.Cells(LastRow, LastCol)).ClearContents
    
End Sub

Regards,

 

[seang87]

Hi satoka,

Thanks this is starting to do the trick.

This code only moves the contents of a single cell "A" how can I modify to move a range "A:R" ?

Thanks again!

 

[seang87]

Sorry..... figured it out.

Thank you so much!!