Creating Summary/ VBA code

[werra2006]

Hi Peeps

I am a complete VBA newbie & have a challenge for you. I’m trying to automate my spreadsheet and populate the worksheet “data capture” with monthly totals from information obtained from the raw data worksheet. What I had done in the past was to build a kind of database in Raw data adding information month by month and then using formula lookups to populate “data capture” totals. In the raw data worksheet, I paste information in columns C to AC and the other columns are formula driven. However, my spreadsheet often crashed and furthermore, I also need to add more worksheets to the workbook.
What I want to do, is be able to put in raw data, a month’s data and run a VBA script to identify the month that the data belongs to & populate totals in data capture for that month. Following month put in again that month’s data & do the same again so that I don’t have to retain every month’s data in the raw data section as the spreadsheet will crash.
I have attached the spreadsheet

[SamT]

Werra,

First, your "Raw Data" is not raw data, it has many formulas in it.

Second we don't need to see 2500 rows of data, just a representative sample.

Third all your formulas use cell references, which are very hard to interpret.

So, we will need to see the general outline of your formulas and enough data samples to know what the data looks like.

Please go thru the book and leave just enough rows for us to get an idea. You might leave one row of each name in the Roll Up Column, because strings can be a problem.

In row one and twoleave all the formulas, delete any in other rows. In Row 1, go thru those formulas and rewrite them as column label references, leaving out any row numbers, because we understand that they apply to all rows. See code below.



Column A
[vba]=CONCATENATE(H2," ",B2,)
becomes
Concatenate "Roll up" and "Report Date"[/vba]

Column AD
[vba]=IF($AY2=AD$1,$AC2,0)
becomes
If "Ageing" = "0-30" then "Balance"[/vba]
I know you'll have fun with columns AX, AY, and AZ. Be sure you leave a working formula as well as your label styled formula, so we have a tested good reference. (Two Rows)

Then, because we need to know, make a two column list, where Col A is the labels and Col B is the Column references.

Roll Up . . . . . .H
Report Date . . .B
Ageing . . . . . .AY
0-30 . . . . . . . AD
Balance . . . . .AC

Then do the same thing for the other two sheets, but you can put the Label-to-Column cross reference in 6 columns on one additional sheet, just be sure to labe for us which pair refers to which sheet.