For Each...won't loop through worksheets
 

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

For Each...won't loop through worksheets
 

try replacing this line

For Each sht In Worksheets

with this

For Each sht In Activeworkbook.Worksheets

HTH


Bill G wrote:
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

For Each...won't loop through worksheets
 

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


--
HTH...

Jim Thomlinson


"Bill G" wrote:

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

For Each...won't loop through worksheets
 

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.


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


--
HTH...

Jim Thomlinson


"Bill G" wrote:

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