Hi guys!

I'm running into a bit of a snag here. I have several workbooks that
contain 20+ worksheets each. I am having a major problem writing a
macro to remove the subtotals from all the worksheets in the work
book. Posted below is a couple macros I have tried. The funny thing
is, that it will remove the subtotals for one sheet only. Yet, if I
use this same looping macro with a different function, such as put
text in A1 on all worksheets, it works like a champ... I don't need
anything fancy, I just need a simple way to remove all subtotals from
all worksheets within the book. Thanks.

Public Sub DoToAll()

'Declare our variable
Dim ws As Worksheet
For Each ws In Worksheets
'place code between the For and Next
'for what you would like to do to
'each sheet
Cells.Select
Selection.RemoveSubtotal
Next
End Sub


I have also tried: This one shows is from Microsoft's website, with
the addition of my remove subtotal section. It does show and have you
click each worksheet name in a pop-up box showing its working, with no
prevail...

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
Cells.Select
Selection.RemoveSubtotal
' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub



And:

Sub Loop
For Each ws In Worksheets
Cells.Select
Selection.RemoveSubtotal

'Update or do something here

Next
End Sub

Sub WorksheetLoop()

Dim WS_Count As Long
Dim I As Long

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
ActiveWorkbook.Worksheets(I).Cells.RemoveSubtotal
Next I

End Sub

or

Sub Loop
dim ws as worksheet
For Each ws In Worksheets
ws.cells.removeSubtotal
Next ws
End Sub

or if you really wanted to select the cells.
Sub Loop
dim ws as worksheet
For Each ws In Worksheets
ws.select
ws.Cells.Select
Selection.RemoveSubtotal
Next ws
End Sub

vidguru wrote:

Hi guys!

I'm running into a bit of a snag here. I have several workbooks that
contain 20+ worksheets each. I am having a major problem writing a
macro to remove the subtotals from all the worksheets in the work
book. Posted below is a couple macros I have tried. The funny thing
is, that it will remove the subtotals for one sheet only. Yet, if I
use this same looping macro with a different function, such as put
text in A1 on all worksheets, it works like a champ... I don't need
anything fancy, I just need a simple way to remove all subtotals from
all worksheets within the book. Thanks.

Public Sub DoToAll()

'Declare our variable
Dim ws As Worksheet
For Each ws In Worksheets
'place code between the For and Next
'for what you would like to do to
'each sheet
Cells.Select
Selection.RemoveSubtotal
Next
End Sub

I have also tried: This one shows is from Microsoft's website, with
the addition of my remove subtotal section. It does show and have you
click each worksheet name in a pop-up box showing its working, with no
prevail...

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
Cells.Select
Selection.RemoveSubtotal
' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub

And:

Sub Loop
For Each ws In Worksheets
Cells.Select
Selection.RemoveSubtotal

'Update or do something here

Next
End Sub

--

Dave Peterson

Thank you SOOOO much!