Input Data/Output data in separate sheets

**boyboyboy** · 01-08-2013, 11:57 PM

ALL,

i NEED help. I am trying to figure out how to create a formula or macro to calculate data and record it to another sheet with a timestamp.

Sheet 1:
defines current number and calculates added values. but once added, it updates current number and records added value to sheet 2 with timestamp

input output.xlsx

**nilem** · 01-09-2013, 12:20 AM

maybe so (in the module of sheet "INPUT SHEET")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "B2" Then Exit Sub
Range("A2") = Range("A2") + Target
If Len(Target) Then Sheets("OUTPUT SHEET").Cells(Rows.Count, 1). _
   End(xlUp)(2, 1).Resize(, 2).Value = Array(Now, Range("A2").Value)
End Sub

**benishiryo** · 01-09-2013, 12:24 AM

hi there, welcome to the forum. i'm assuming your Sheet 1 only consists of A1:B2 & the rest are for illustration? if so, try:

Sub test()
Set WS_I = Sheets("INPUT SHEET")
Set WS_O = Sheets("OUTPUT SHEET")

WS_I.Range("A2") = WS_I.Range("A2") + WS_I.Range("B2")
WS_O.Range("A" & WS_O.Cells(Rows.Count, 1).End(xlUp).Row + 1) = Format(Now, "d mmm yy hhmm")
WS_O.Range("B" & WS_O.Cells(Rows.Count, 2).End(xlUp).Row + 1) = WS_I.Range("A2")
WS_I.Range("B2").ClearContents

End Sub

or maybe a change without even clicking the macro:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set WS_I = Sheets("INPUT SHEET")
Set WS_O = Sheets("OUTPUT SHEET")

WS_I.Range("A2") = WS_I.Range("A2") + WS_I.Range("B2")
WS_O.Range("A" & WS_O.Cells(Rows.Count, 1).End(xlUp).Row + 1) = Format(Now, "d mmm yy hhmm")
WS_O.Range("B" & WS_O.Cells(Rows.Count, 2).End(xlUp).Row + 1) = WS_I.Range("A2")
WS_I.Range("B2").ClearContents
End Sub