Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting sheets 2&3 | Excel Discussion (Misc queries) | |||
deleting sheets | Excel Discussion (Misc queries) | |||
Deleting Sheets | Excel Programming | |||
Deleting sheets | Excel Programming | |||
deleting sheets | Excel Programming |