Using Subtotal levels in code (2000 SR-1)

Topic

New Reply

Hi All!

I’m hoping someone can help me… I’ve been working on creating a different way of allocating interest inventory for my company… It took a long time but I’ve finally got the old way to tie in to the new way…
Yahoo!!! I’m balanced!!!

What I need from you is to know if… I have added 2 or more levels of Subtotals on a sheet… By Currency, then Dept, etc… and another sheet with different levels of Subtotals… Can I, in code, create a new table on a third sheet that uses the different subtotal levels to compare/balance…

What I mean is… I hit the button I created and code runs that drags in the records I want and then adds the levels of subtotals…
Now I’d like to go to a third sheet and list the currencies, depts, etc, and bring in subtotal data from the other sheets to compare and show balanced…

In code can I say something like…
Find Sheet1’s Canadian Currency section, Dept 001’s Total and put it here… Next to it put Sheet2’s Canadian Currency section Dept 001’s Total… etc…

I’m sorry… I don’t think I’m making sense….

Okay… Let’s try this… The code below makes the subtotals after the new data is updated… (It works, but please feel free to edit this if I’m doing something silly…)

Range(“A2”).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Range(“B2”).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Can I now use something like Sheet3, Cell(whatever) = Selection.Subtotal Group1?… or do I have to search for the lines with the words “Dept 001 Total”, etc and offset to the right column?

in Advance for any ideas!!!

Reply | Quote

Replies

Normally I’m against posting a workbook with the problem at hand, but in this case:

Could you post a small sample workbook with bogus information that is already set up the way you would like it (that is, the end result and some explanation)?

Reply | Quote

Thanks Jan… I’ll try put something together for an example…

The workbook has about 20 sheets with countless named ranges referring to one another, so at the moment I’m not sure where to begin the explanation…
By the way, I didn’t create this mess… I’m just the person responsible for making changes before year end…
Personally I’d rather take the whole thing, scrap it, and do the work in a normalized database… The problem there is that I don’t have the time…

Anyway… I’ll post back when I have something to show you….

Reply | Quote

[indent]

---

countless named ranges

---

[/indent]

RUN to “The Excel MVP page” listed below and download my Name Manager.

Reply | Quote

Thanks again Jan! I downloaded and installed the Name Manager…
There are 1808 named ranges in this workbook…

Somebody kill me now!!

Reply | Quote

[indent]

---

1808 named ranges

---

[/indent]
Wow.

You might try to get rid of names not in use.

– Make a backup copy of the book.
– Then you could try filtering using the Unused names checkbox (go get a cup of coffee after checking it, it’ll take a while).
– Select all the now left over names (first click the Multi button, then drag accross the names list) and press delete.
– Now check the book for any #Name! errors. Especially check all objects (charts, pivot tables, conditional formatting, etcetera). If any got messed up, reload the backup copy.

Reply | Quote

[indent]

---

1808 named ranges

---

[/indent]
Wow.

You might try to get rid of names not in use.

– Make a backup copy of the book.
– Then you could try filtering using the Unused names checkbox (go get a cup of coffee after checking it, it’ll take a while).
– Select all the now left over names (first click the Multi button, then drag accross the names list) and press delete.
– Now check the book for any #Name! errors. Especially check all objects (charts, pivot tables, conditional formatting, etcetera). If any got messed up, reload the backup copy.

Reply | Quote

Thanks again Jan! I downloaded and installed the Name Manager…
There are 1808 named ranges in this workbook…

Somebody kill me now!!

Reply | Quote

[indent]

---

countless named ranges

---

[/indent]

RUN to “The Excel MVP page” listed below and download my Name Manager.

Reply | Quote

Thanks Jan… I’ll try put something together for an example…

The workbook has about 20 sheets with countless named ranges referring to one another, so at the moment I’m not sure where to begin the explanation…
By the way, I didn’t create this mess… I’m just the person responsible for making changes before year end…
Personally I’d rather take the whole thing, scrap it, and do the work in a normalized database… The problem there is that I don’t have the time…

Anyway… I’ll post back when I have something to show you….

Reply | Quote

If I understand what you are asking (and I admit, I am a little confused):
I think you can do what you want, by NOT even doing any subtotaling and grouping or a macro.
You should be able to do it DIRECTLY from your data with a pivot table report.
Pivot table will extract out a summary of the data, by the various sections (ROW field) or section by section (PAGE field) depending on how you set it up.

Data-pivot table report gets you the wizard.
Once you create the privot table, you can right click on it to refresh the table (with new “raw data” added) or to modify the layout.

If I do NOT understand, I also second Jan’s suggestion to provide a simple example with more detailed explanation of what you would like, perhaps an example “output table” that you want to generate from the raw table.

Steve

Reply | Quote

If I understand what you are asking (and I admit, I am a little confused):
I think you can do what you want, by NOT even doing any subtotaling and grouping or a macro.
You should be able to do it DIRECTLY from your data with a pivot table report.
Pivot table will extract out a summary of the data, by the various sections (ROW field) or section by section (PAGE field) depending on how you set it up.

Data-pivot table report gets you the wizard.
Once you create the privot table, you can right click on it to refresh the table (with new “raw data” added) or to modify the layout.

If I do NOT understand, I also second Jan’s suggestion to provide a simple example with more detailed explanation of what you would like, perhaps an example “output table” that you want to generate from the raw table.

Steve

Reply | Quote