I have a routine where I insert two temp sheets, name
them and then copy and paste data to them as I loop
through my workbooks sheets in a For Each Loop. The Loop
was intended to be set up so that it would loop through
all the sheets in the file EXCEPT the two temp sheets. I
only have one sheet in the file aside from the two temp
sheets. However when looping, the temp sheets get
included in the loop. Can't figure out why. The code is
not adhering to the contraint:

If sh.Name < sh2.Name Or sh.Name < sh3.Name Then

When I test the sheet names after the first sheet loop in
the Immediate window I get:
?sh.Name
KazAuditUFTempH
?sh2.Name
KazAuditUFTempH

Should this not make the contraint hold? The code is
below:

Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim TempSh2Name As String
Dim TempSh3Name As String

'Pass names to new temp sheets
TempSh2Name = "AuditUFTempH"
TempSh3Name = "AuditUFTempV"

On Error Resume Next
Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)

'If first temp sheet exists, delete it
If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

'If second temp sheet exists, delete it
If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If

'Now add two new temp sheets
With ActiveWorkbook
..Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh2Name

..Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh3Name
End With

Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)

For Each sh In ActiveWorkbook.Worksheets
If sh.Name < sh2.Name Or sh.Name < sh3.Name Then
'Lots of code here

End if
Next

If sh.Name < sh2.Name Or sh.Name < sh3.Name Then

You want an And condition here, not an Or. With Or, one of the
conditions will always be true.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"ExcelMonkey" wrote in
message ...
I have a routine where I insert two temp sheets, name
them and then copy and paste data to them as I loop
through my workbooks sheets in a For Each Loop. The Loop
was intended to be set up so that it would loop through
all the sheets in the file EXCEPT the two temp sheets. I
only have one sheet in the file aside from the two temp
sheets. However when looping, the temp sheets get
included in the loop. Can't figure out why. The code is
not adhering to the contraint:

If sh.Name < sh2.Name Or sh.Name < sh3.Name Then

When I test the sheet names after the first sheet loop in
the Immediate window I get:
?sh.Name
KazAuditUFTempH
?sh2.Name
KazAuditUFTempH

Should this not make the contraint hold? The code is
below:

Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim TempSh2Name As String
Dim TempSh3Name As String

'Pass names to new temp sheets
TempSh2Name = "AuditUFTempH"
TempSh3Name = "AuditUFTempV"

On Error Resume Next
Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)

'If first temp sheet exists, delete it
If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

'If second temp sheet exists, delete it
If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If

'Now add two new temp sheets
With ActiveWorkbook
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh2Name

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh3Name
End With

Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)

For Each sh In ActiveWorkbook.Worksheets
If sh.Name < sh2.Name Or sh.Name < sh3.Name Then
'Lots of code here

End if
Next

Thanks Chip. Been pulling my hair out for an hour.

Kind Regards

EM


-----Original Message-----
If sh.Name < sh2.Name Or sh.Name < sh3.Name Then

You want an And condition here, not an Or. With Or, one
of the
conditions will always be true.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"ExcelMonkey"
wrote in
message ...
I have a routine where I insert two temp sheets, name
them and then copy and paste data to them as I loop
through my workbooks sheets in a For Each Loop. The
Loop
was intended to be set up so that it would loop through
all the sheets in the file EXCEPT the two temp
sheets. I
only have one sheet in the file aside from the two temp
sheets. However when looping, the temp sheets get
included in the loop. Can't figure out why. The code
is
not adhering to the contraint:

If sh.Name < sh2.Name Or sh.Name < sh3.Name Then

When I test the sheet names after the first sheet loop
in
the Immediate window I get:
?sh.Name
KazAuditUFTempH
?sh2.Name
KazAuditUFTempH

Should this not make the contraint hold? The code is
below:

Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim TempSh2Name As String
Dim TempSh3Name As String

'Pass names to new temp sheets
TempSh2Name = "AuditUFTempH"
TempSh3Name = "AuditUFTempV"

On Error Resume Next
Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)

'If first temp sheet exists, delete it
If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

'If second temp sheet exists, delete it
If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If

'Now add two new temp sheets
With ActiveWorkbook
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh2Name

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh3Name
End With

Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)

For Each sh In ActiveWorkbook.Worksheets
If sh.Name < sh2.Name Or sh.Name < sh3.Name
Then
'Lots of code here

End if
Next


.