agnesz
06-17-2008, 06:50 AM
I have the following code that needs to run through about 10 sheets that are subtotaled. First the code needs to remove subtotals and then delete specific rows. For some reason, the code only works on the fisrt tab??? Any ideas why? This is so frustrating!!!!!!!!!!! Please help!
Sub WorksheetLoop()
Windows("MondayMorningTemplate.xls").Activate
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
SetUpReport
' 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
Sub SetUpReport()
ActiveSheet.Outline.ShowLevels RowLevels:=3
Cells.Select
Selection.RemoveSubtotal
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(8).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
Select Case .Value
Case Is = "3<= 80%", "4<= 100%", "5 NEW", "Total Excluding New Receipts / Cont % TTL": .EntireRow.Delete
End Select
End If
End With
Next Lrow
End With
Calculate
End Sub
Sub WorksheetLoop()
Windows("MondayMorningTemplate.xls").Activate
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
SetUpReport
' 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
Sub SetUpReport()
ActiveSheet.Outline.ShowLevels RowLevels:=3
Cells.Select
Selection.RemoveSubtotal
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(8).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
Select Case .Value
Case Is = "3<= 80%", "4<= 100%", "5 NEW", "Total Excluding New Receipts / Cont % TTL": .EntireRow.Delete
End Select
End If
End With
Next Lrow
End With
Calculate
End Sub