Month End Tracker

Question

Hi I have a spreadsheet with the following values:

       A                    B                C             D            E

Active Clients     Cost Per Client         Total         Month         Value
       5                 £100           =Sum(A3*B3)       Nov
                                                          Dec
                                                          Jan

The active clients are linked to another spreadsheet, so this value could potentially change each month.

What I need is that at the end of each month e.g. Nov the Total in C is transfered and locked in Column E. So Nov, Dec , Jan might have different Values. I understand I could just copy and paste it at as vaules at the end of each month, but I'm not going to be using it, and the person who is want's it done automatically.

I'm guessing it'll need VBA but I know nothing about it.

Any Help would be really appreciated.

Thanks

Answer 1

A great way to learn Excel VBA is to record an Excel macro and then examine the code it generates (NOTE: I'm not sure if this works in Excel 2007, but it definately works in 2003 and XP).

• Figure out exactly what you want to do and write down the steps (e.g. Select values, Edit-Copy, Click on Destination Sheet, Click in correct area, Edit-Paste Values).
• Then go to Tools > Macro > Record New Macro (Excel 2002/XP)
• After doing all the actions you want to do, stop the macro.
• Then go to Tools > Macro > Visual Basic Editor
• On the left side of the Visual Basic Editor, find "Module1" in the Modules folder.
• You'll see the code for your macro in there.

Perhaps there are other things you may need to learn to polish the VBA code in that macro. For example, if the number of items you need to select changes, you'll need to write code for that. Another example: if you want to bind the macro to when the user clicks a button, you'll need to write code for that.

Try recording the macro, then let us know exactly where you need help in VBA, and we can try to help you figure out the rest of your code.

I hope this helps.