Add to Total and Clear Macro

Hi all.

I'm relatively new to Excel and especially to macros. The spreadsheet I'm working on deals with inventory levels, and I want to write a macro to connect two worksheets. The first sheet is updated weekly with new purchases, and the first sheet is supposed to be a cumulative total of inventory. So, I am trying to write a macro that would add the quantity on sheet one to the quantity on sheet two, and then clear sheet one. This will be done every week to keep a running tally of inventory. Any idea how this could be done?

Thank you for any help, and I'm sorry if a similar question has already been asked.

Hi abcd12,
If your quantity on Sheet1 is in A1 and you want your running total to be in A1 of Sheet2, try this code. You can put in in the ThisWorkbook section of VB editor.

Dim Count As Long

Sheets("Sheet1").Activate
Count = Range("A1")
Sheets("Sheet2").Activate
Range("A1") = Range("A1") + Count
Sheets("Sheet1").Activate
Range("A1").ClearContents

Thank you very much for your help. How can I apply this macro to the entire sheet?

Do you mean have every cell from Sheet2 do a running total from the corresponding cell in Sheet1 and then clear Sheet1?

Originally Posted by wmorrison49

Do you mean have every cell from Sheet2 do a running total from the corresponding cell in Sheet1 and then clear Sheet1?

Yes that what I mean, if it's possible.

And thanks for your help, wmorrison

Well, it's not beautiful code, but it works. You'll need to have your new data in Sheet1, your previous running total in Sheet2, and then your up to date running total will be in Sheet3. Here's the code:

Sheets("Sheet3").Activate
Range("A1").Select
ActiveCell = "=Sheet1!A1+Sheet2!A1"
Selection.AutoFill Destination:=Range("A1:A1000"), Type:=xlFillDefault
Range("A1:A1000").Select
Selection.AutoFill Destination:=Range("A1:AZ1000"), Type:=xlFillDefault
Range("A1:AZ1000").Select

Range("A1:AZ1000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

hmmm...that gives me a Run Time 4001 Error, can't seem to figure out what's wrong