macro that changes cell reference

[Chenboy2]

Using two sheets, one sheet to reference the cells and the other to output the data, I'm trying to see if I can sum a different set of cells each time I run the macro and only adding up the cells with the newly entered data. In addition, previous data will not be summed again. For example, if cells B1:C3 has been summed, the next time I want cells B4:C7 summed, granted that there are numbers in that range of cells. The macro would also need to be able to determine where to start based on the new date in a different cell.

For example, if on Sheet 1 column A was the date column, columns B and C were the two columns with the data, the macro would first reference and copy or reference the date in column A from Sheet 1 into Sheet 2 into cell A1 and then sum columns B and C into cell A2. The next time the same macro is ran, the next date is copied and the data for the corresponding date is copied onto cell A2 and B2, likewise.

Is this even possible to do? Does Excel macros do this?

I hope this makes sense without any screen prints. Please let me know if clarification is needed.

Thanks.

 

[venkat1926]

If I understood you correctly this macro may help you (main data in sheet 1 and results in sheet2)

Sub test()
Dim r As Range, x As Double
With Worksheets("sheet1")
Set r = .Cells(Rows.Count, "A").End(xlUp)
x = r.Offset(0, 1) + r.Offset(0, 2)
With Worksheets("sheet2")
.Range("A1") = r
.Range("A1").NumberFormat = "[$-409]d-mmm-yy;@"
.Range("A2") = x
End With
End With
End Sub