Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Globally Remove all Sub-Totals (XL 2003)

    Can someone supple a bit of code that will remove all sub-totals from selected sheets? I would like to attach such code to a toolbar button.

    I have a special need for such a button and I realize it probably wouldn't appeal to everyone... Monthly, I get a workbook with 31 worksheets. Each worksheet is loaded with subtotals. To remove the subtotals, I have to select each sheet individually, select the appropriate range, click Data| SubTotals...| OK| Remove All. And then repeat 30 more times...

    I'd like to be able to select all of the sheets and then click a toolbar button to remove all the subtotals from all the sheets in one fell swoop.
    - Ricky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Globally Remove all Sub-Totals (XL 2003)

    If you want to remove subtotals from *all* sheets (it doesn't matter if some sheets don't have subtotals):

    Sub RemoveSubTotalsFromAllSheets()
    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    wsh.Cells.RemoveSubtotal
    Next wsh
    End Sub

    If you want to remove subtotals from the currently selected sheets only:

    Sub RemoveSubTotalsFromSelectedSheets()
    Dim wsh As Worksheet
    For Each wsh In ActiveWindow.SelectedSheets
    wsh.Cells.RemoveSubtotal
    Next wsh
    End Sub

    If you store the macro you want in your personal macro workbook Personal.xls, it will be available in all workbooks. You can assign it to a toolbar button and/or keyboard shortcut.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Globally Remove all Sub-Totals (XL 2003)

    You make it look so simple...
    I used the second example and ended up attaching the macro to a new menu choice just below Data|Subtotals... (named it Remove Subtotals).

    Thanks,
    - Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •