Selected Answer
The way to go about this is to first create the system and then, in the second step, automate it. Most novices will try to do both at the same time. That will lead to a flawed design which causes permanent trouble and the only cure being to start afresh.
Your design appears correct but might require a slightly different view. Consider describing it like this:-
- There is one sheet containing the budget.
I would recommend it to be an annual budget, perhaps with a column for each month for each category. This sheet doesn't need the actual.
- There is another sheet with actual expenditure where each item is identified with date, category and amount. This sheet would an endless database, entries not separated by month but identified by date. If you absolutely must, start a new sheet at the beginning of a year but chances are that you will soon find that troublesome.
- There is a third sheet for reconciliation. This sheet takes the budget from the first sheet and summarizes the actuals from the second. You will want to be able to define a month for reconciliation or a group of months, up to a year.
- There may be a fourth "sheet" which shows the budget per month, or group of months. This would be an extract from the first sheet to give a different presentation, probably in portait format where the first sheet is landcape becauswe of its many columns. I refer to it as "sheet" because it really isn't. It's the template for a report. You would use code to extract data from the budget sheet and write them into the template sheet which contains the cell formatting (to reduce the amount of code needed).
Code can only be written when the design (number of columns, column captions, cell formatting) is complete. It consists then of finding a data row and copying the value from one column to a particular cell on another sheet. VBA makes this very easy. You will need code like
Worksheets("Reconciliation").Cells([Row], [Column]).Value = Worksheets("Budget").Cells([Row], [Column]).Value
or
MySum = MySum + Worksheets("Budget").Cells([Row], [Column]).Value
and
Worksheets("Reconciliation").Cells([Row], [Column]).Value = MySum
To get started, make sure that everything you need to refer to has a name. Then describe what you want summed up and written where in plain English, and someone will show you the syntax. In next to no time you will be able to do it on your own.
