How do you create multiple charts/reports for 1 filtered set of raw data in excel?

I am making a set of reports for my job in Excel 2007.

I have been given a set of raw data, which is organized into one excel table. This raw data will change, so the reports I'm trying to make need to allow this. I have also been given a set of template charts. These are to be used to reflect results from 22 different groups within my raw data, separately. For each group the charts are the same, but a new worksheet (with the set of charts) must be created for each group.

I have spent alot of time trying to do this and I think I'm going about this the wrong way. This is a summary of a macro that I made that is close to giving me the end result (which I ended up copying and pasting for all groups).

1. Filter Raw Data for group only.
2. Copy the entire sheet.
3. Paste to another worksheet as values only. (this gives me an error about using too many resources)

So now, for each group, I have a chart worksheet and sheet that has the copied filter results. Then, I made a third sheet for the chart d开发者_如何学Pythonata. This sheet has many formulas that run calculations on the filtered data for each chart. The charts reference these.

Is this really the easiest way to do something like this? It has been alot of work to copy the macro for each group. Assign data to each chart for each group. Plus I have like 67 worksheets in my workbook now. Is this something pivot tables could do much easier?

---

I assume that the groups are predefined? If so, I would create dynamic spreadsheets.

1. On the main page create a filter and add a subroutine something like:

   Public Sub SetAutoFilter(ByVal GroupName As String)
   
     Me.AutoFilterMode = False
   
     Dim intRow As Long: intRow = Me.Range("B65536").End(xlUp).Row
   
     If intRow = 1 Then Exit Sub 'nothing to filter
   
     With Me.Range(Cells(1,1), Cells(intRow, 23)) '23 columns
       .AutoFilter
       .AutoFilter Field:=1, Criteria1:=GroupName
     End With
   
   End Sub
   

   That way you will have a code to filter the data and you would not have to copy it over and over.

2. Create a second worksheet with a dropdown which contains all groups and a chart. Once a selection in the drop down cahnges, you call a proceduer from step one.

3. The most tricky bit - I don't know how complicated your calculations are, but probably you would like to write another subroutine which process the filtered data from the first worksheet. The routine outputs the calculation to the third spreadsheet (can be hidden if needed), so you can easily verify the values.

   The simple summing/averaging/counting etc will look like:

   GetPositionCount = Application.WorksheetFunction.Subtotal(2, Me.Columns(7))
   

   which works on the filtered data.

4. Finally you have to wire up the chart to the calculated data.

5. Optional step but users seems to be very kin on it. Add another button which will print all reports in one go. It is very easy - just loop through groups, get the report displayed and fire:

   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, To:=1
   

HTH. (The code is taken from the real life solution, so hopefully it works)