Creating a large number of independent summaries from database information

Hello! This is my first time using the forum.

I need to summarize some data from a database based on multiple different sets of criteria (ex. sum of Blue + 7 + True, sum of Blue + 8+ True, sum of Blue + 7 + False, sum of Blue + 8 + False). Generally, I'd probably do this with a pivot table, but I need to save a lot of different summaries, and I need to be able to plop new data in the original range and have the summaries automatically recalculate.

Here is the formula I am using, which I want to be able to auto fill with a macro. This appears in C25 and needs to be filled to C35 of my summary tab. Each cell would be calculated based on a different, subsequent set of criteria.

=DSUM('Raw Data'!$A$19:$U$14972,"Sum This Column",Criteria!A36:U37)

The criteria it is referencing is 2 rows high and 21 rows long. I stored different permutations of criteria every 3rd row.

So the next cell (C26) should be automatically populated with =DSUM('FY08 (2)'!$A$19:$U$14972,"Any hires",Criteria!A39:U40)

Here is a general example of one set of criteria:

Location	Level of health	Production	Production	Age	Requires full sun (Y/N)	Number of occurrences
Michigan	Medium	>70	<75	50	N	3

I have about 20 of these for each 2R by 10C summary table. There are 5 unique summary tables, so 100 different sets of criteria. These summaries must be calculated for multiple data sets.

After finishing the C25:C35 summary, I'd move to C39 and start another summary table based on yet another series of subsequent lists of criteria.


AND SO MY QUESTION: I was hoping there might be a way to do this with macros. Am I just dumb, is there a much easier way to do this? It seems way too big to do it manually. I also looked in to doing it with formulas, but everything seemed aimed at being able to offset from a cell you are referencing, not a whole range that includes text data.

Here is what I have tried so far:

Sub CopyingFormula()
Dim Rng As Range
For Each Rng In Application.Selection
i = 36
Cells(Rng.Row, Rng.Column) = "=DSUM('FY08 (2)'!A19:U14972,""any hires"",'Criteria'!A"& i &":U "& i+1 &")
i=i+3
Next Rng

End Sub

This gives me a compile error.
I have no idea what I'm doing and I don't really know VBA. I have a book on it, and google. :confused:
Let me know if I need to clarify anything.

Hi,

I am wondering if you can achieve this using Pivot tables? if you do it would be easier to set up all the pivot tables you have a then just refresh them.

Can you provide a sample of the workbook. remove all sensitive data

Thanks

Well, my concern with pivot tables is that I'd need to create at least 20 separate pivot tables for the data set, which would make the file quite large, then I'm assuming I'd need to copy and paste those values somewhere so that I can put the new data into the table, but that would mean that the original results wouldn't update if that source data is altered...

Some of the back data would be unlikely to change, so pivot tables are a possibility if I can't get something else to work. At this point, pivot tables are my fallback option.