Deleting Sheets
 

I have a routine which adds sheet to a workbook. It
first detects if they are already there. If so, it
deletes them, then it creates names for them and adds
them back in. At the end of the routine I want to delete
them again. I am using the exact code for deleting the
sheets at the end of the routine as I am at the start.
However the sheets do not get deleted at the end of the
routine. When I typein ?sh2, I get a run time error.
Whey is this?

TempSh2Name = "AuditUFTempH"
TempSh3Name = "AuditUFTempV"

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

If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If

With ActiveWorkbook
..Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh2Name

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


If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If

Deleting Sheets
 

When you delete the sheets, the object variable loses its reference, so it
is effectively cleared. You cannot refer to that object variable later and
expect it to still be pointing at the object . Just because an object has
the same name as a previous object, it is not the same object.

You need to recreate your object variables

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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote in message
...
I have a routine which adds sheet to a workbook. It
first detects if they are already there. If so, it
deletes them, then it creates names for them and adds
them back in. At the end of the routine I want to delete
them again. I am using the exact code for deleting the
sheets at the end of the routine as I am at the start.
However the sheets do not get deleted at the end of the
routine. When I typein ?sh2, I get a run time error.
Whey is this?

TempSh2Name = "AuditUFTempH"
TempSh3Name = "AuditUFTempV"

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

If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If

With ActiveWorkbook
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh2Name

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


If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If