Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a Worksheet??
I have a macro that, depending on the data, will sometimes create a separate
sheet entitled "MC TABLE". I also have a second Macro within the same program that will delete and clear the needed sections of the different worksheets so that it can be ready for the next batch of data on the next day. The problem is that the "MC TABLE" sheet needs to be deleted entirely but on the occasions that it does not exist, I get an error message. How can I write the code so that it tests to see if that sheet exists before attempting to select or delete it?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a Worksheet??
Try this
You don't need to check, simply do this Sub marine3() Application.DisplayAlerts = False On Error Resume Next Sheets("MC TABLE").Delete Application.DisplayAlerts = True On Error GoTo 0 End Sub But if uo want to Sub marine() Dim WS As Worksheet On Error Resume Next Set WS = Sheets("MC TABLE") On Error GoTo 0 If Not WS Is Nothing Then MsgBox "I exist" Application.DisplayAlerts = False WS.Delete Application.DisplayAlerts = True Else MsgBox "I don't exist" End If End Sub Mike "Scott" wrote: I have a macro that, depending on the data, will sometimes create a separate sheet entitled "MC TABLE". I also have a second Macro within the same program that will delete and clear the needed sections of the different worksheets so that it can be ready for the next batch of data on the next day. The problem is that the "MC TABLE" sheet needs to be deleted entirely but on the occasions that it does not exist, I get an error message. How can I write the code so that it tests to see if that sheet exists before attempting to select or delete it?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a Worksheet??
Read the tips of John Walkenbach: very usefull!!!!!!
http://spreadsheetpage.com/index.php...vba_functions/ Success!!! -- met vriendelijke groetjes "Scott" schreef in bericht ... I have a macro that, depending on the data, will sometimes create a separate sheet entitled "MC TABLE". I also have a second Macro within the same program that will delete and clear the needed sections of the different worksheets so that it can be ready for the next batch of data on the next day. The problem is that the "MC TABLE" sheet needs to be deleted entirely but on the occasions that it does not exist, I get an error message. How can I write the code so that it tests to see if that sheet exists before attempting to select or delete it?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a Worksheet??
Thank you to both. Both answers proved useful for this question and another
issue I was working on. Scott "Scott" wrote: I have a macro that, depending on the data, will sometimes create a separate sheet entitled "MC TABLE". I also have a second Macro within the same program that will delete and clear the needed sections of the different worksheets so that it can be ready for the next batch of data on the next day. The problem is that the "MC TABLE" sheet needs to be deleted entirely but on the occasions that it does not exist, I get an error message. How can I write the code so that it tests to see if that sheet exists before attempting to select or delete it?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a Worksheet??
Hi Mike,
The short version worked great but was wondering what "On Error GoTo 0" actually does?? The rest made sense to me such as "On Error Resume Next" just means if an error occurs on a line then skip it and start with the next line of code. Thanks, Scott "Mike H" wrote: Try this You don't need to check, simply do this Sub marine3() Application.DisplayAlerts = False On Error Resume Next Sheets("MC TABLE").Delete Application.DisplayAlerts = True On Error GoTo 0 End Sub But if uo want to Sub marine() Dim WS As Worksheet On Error Resume Next Set WS = Sheets("MC TABLE") On Error GoTo 0 If Not WS Is Nothing Then MsgBox "I exist" Application.DisplayAlerts = False WS.Delete Application.DisplayAlerts = True Else MsgBox "I don't exist" End If End Sub Mike "Scott" wrote: I have a macro that, depending on the data, will sometimes create a separate sheet entitled "MC TABLE". I also have a second Macro within the same program that will delete and clear the needed sections of the different worksheets so that it can be ready for the next batch of data on the next day. The problem is that the "MC TABLE" sheet needs to be deleted entirely but on the occasions that it does not exist, I get an error message. How can I write the code so that it tests to see if that sheet exists before attempting to select or delete it?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting of worksheet | Excel Discussion (Misc queries) | |||
Deleting column in a worksheet that is not active worksheet | Excel Programming | |||
Deleting a worksheet but retaining values from the worksheet. | Excel Discussion (Misc queries) | |||
Deleting a worksheet but retaining values from the worksheet. | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions |