V
vidguru
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
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