how do I do a autosum after doing a copy and paste function

[ghrek]

Hi

I have a workbook that has 2 sheets the same and what im trying to do is when Ive put the data in sheet 1 I click a macro button and it transfers the data to sheet 2 of which is exactly the same as in layout.

What I need it to do then is when I click on the macro button all future times I need the value from sheet 1 added to the current value in sheet 2

let say cell B4 on sheet 1 was £1 that carried over to B4 in sheet 2 on the first occasion. Then when I do it next time the value in B4 in sheet 1 will be lets say £2 so I need that added to value of cell B4 on sheet 2 and shown that so therefore B4 in sheet 2 would be £3.

Any Ideas?

 

[ThanP]

Hi,

I guess you want an incrementation
You can use this :

Option Explicit
Dim NbToSum, CumulSum As Currency 'Just guessing you will be using pound

Sub SumNumber()
    NbToSum = Sheets("Sheet1").Range("B4").Value 'Amount to add
    CumulSum = Sheets("Sheet2").Range("B4").Value 'Sum of the amounts
    
    If IsNumeric(NbToSum) = False Then 'Just to be sure it is a number
        MsgBox "Please enter a number in Cell B4"
        Sheets("Sheet1").Range("B4").ClearContents
        Exit Sub
    Else
        CumulSum = CumulSum + NbToSum 'Add the new amount
        Sheets("Sheet2").Range("B4").Value = CumulSum 'Show in Sheet2 B4
        Sheets("Sheet1").Range("B4").Value = 0 'Reset the value
    End If
End Sub

This is just an idea, your needs might be a lot more complicated.

ThanP

 

[ghrek]

Hi

Apologies for delay. Yes it does exactly what I want it to do but just need 1 amendment.

Are you able to set it up do it does data in cells B7-B32 and E7-E32 all at the same time ?

 

[ghrek]

Any Ideas on above anyone?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Please supply all relevant links.

 

[ghrek]

Am I able to do a formula for whole column on excel sheet

HI I have a workbook that is adding running totals on a 2nd sheet. At present I have it for one cell only but im trying to get the macro to run on all data in cells B7-B32 and E7-E32 VBA Code: Option Explicit Dim NbToSum, CumulSum As Currency 'Just guessing you will be using pound...

www.excelforum.com