E
ExcelMonkey
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
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