For Each...won't loop through worksheets

  • Thread starter Thread starter Bill G
  • Start date Start date
B

Bill G

What am I missing here? Below is some copy/pasted code in an attempt to have
a Sub run on each worksheet of a workbook. I have some more involved
reformatting routines that I'd like to run as well on these sheets, but I
figured I'd start here. I can't seem to get past a basic point.

I know there are more ways than one to skin this cat & I'm not in good form
w/o declarations - please indulge me...

When this code runs in a workbook with worksheets named:

Summary, Sun, Mon, Tue, Wed, Thu, Fri, Sat

....it only does anything if the active sheet is something other than
'Summary' and then it deletes 7 rows from the Active sheet. What am I
missing that's not allowing the first row of each sheet to be deleted (I
actually want to delete the first 11).

Sub DeleteOldHeader()

Dim sht As Worksheet

Dim target As Worksheet

Set target = ActiveWorkbook.Worksheets("Summary")

For Each sht In Worksheets

If sht.Name <> target.Name Then

Rows(1).Delete

End If

Next sht

End Sub
 
try replacing this line

For Each sht In Worksheets

with this

For Each sht In Activeworkbook.Worksheets

HTH
 
The issue that you are running into is that if you are not working with the
active sheet then you need to be specific as to which sheet you want to
modify... Where you try to delete the row you do not specifiy which sheet so
the action is performed on the active sheet. Give this a try...

Sub DeleteOldHeader()
Dim sht As Worksheet
Dim target As Worksheet
Set target = ActiveWorkbook.Worksheets("Summary")
For Each sht In Worksheets
If sht.Name <> target.Name Then
sht.Rows(1).Delete
End If
Next sht
End Sub
 
Thanks so much - now I'll see if the rest of my knows-enough-to-be-dangerous
stuff works.

This group is very much appreciated.
 
Back
Top