I currently have a workbook that has 10 worksheets. Nine of the 10
worksheets are brought in from another application. The 10th worksheet is
added at the beginning as a summary worksheet for the other 9. At this time
someone has created a macro to look in column H of worksheets 2 - 10 (may not
always be 10 worksheets, could be more, could be less) for a value of 1.00
and if it exists to delete that row. While this works great, it has to be
manually applied to all 9 worksheets in the workbook. Is there a way to get
the macro to run for all worksheets except the first one?

Thanks in advance!

Elizabeth,

Thos goes in a module. Alt +F11 to open VB editor. Right click 'This
Workbook' insert module and paste this in:-

Sub sonic()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If ws.Name < "Sheet1" Then ' Change to your sheet
'do your stuff
End If
Next ws
End Sub

Mike

"Elizabeth" wrote:

I currently have a workbook that has 10 worksheets. Nine of the 10
worksheets are brought in from another application. The 10th worksheet is
added at the beginning as a summary worksheet for the other 9. At this time
someone has created a macro to look in column H of worksheets 2 - 10 (may not
always be 10 worksheets, could be more, could be less) for a value of 1.00
and if it exists to delete that row. While this works great, it has to be
manually applied to all 9 worksheets in the workbook. Is there a way to get
the macro to run for all worksheets except the first one?

Thanks in advance!

Sub delete_ones()
Dim c As Range
Dim n As Single
Application.ScreenUpdating = False
For n = 2 To Sheets.Count
With Sheets(n).Columns("H")
Do
Set c = .Find(1, LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 14 Feb 2008 10:15:01 -0800, Elizabeth
wrote:

I currently have a workbook that has 10 worksheets. Nine of the 10
worksheets are brought in from another application. The 10th worksheet is
added at the beginning as a summary worksheet for the other 9. At this time
someone has created a macro to look in column H of worksheets 2 - 10 (may not
always be 10 worksheets, could be more, could be less) for a value of 1.00
and if it exists to delete that row. While this works great, it has to be
manually applied to all 9 worksheets in the workbook. Is there a way to get
the macro to run for all worksheets except the first one?

Thanks in advance!

Thank you both so much for your responses. They are very helpful. I'm
looking forward to learning, and working with, Excel for the first time in my
life!

Thanks again!

"Gord Dibben" wrote:

Sub delete_ones()
Dim c As Range
Dim n As Single
Application.ScreenUpdating = False
For n = 2 To Sheets.Count
With Sheets(n).Columns("H")
Do
Set c = .Find(1, LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 14 Feb 2008 10:15:01 -0800, Elizabeth
wrote:

I currently have a workbook that has 10 worksheets. Nine of the 10
worksheets are brought in from another application. The 10th worksheet is
added at the beginning as a summary worksheet for the other 9. At this time
someone has created a macro to look in column H of worksheets 2 - 10 (may not
always be 10 worksheets, could be more, could be less) for a value of 1.00
and if it exists to delete that row. While this works great, it has to be
manually applied to all 9 worksheets in the workbook. Is there a way to get
the macro to run for all worksheets except the first one?

Thanks in advance!