Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If worksheet exists delete
I've rcorded some code that loops through a table and creates a chart on each column, the chart worksheet is named after a variable myshrttitle which is driven by a cell value. The code works fine first time and creates all the charts, what i want to achieve is when the code runs next time to delete the worksheet with name myshrttitle and then recreate it? Is this the correct way to go about the problem? I name the sheet with the following ActiveChart.Location xlLocationAsNewSheet, myshrttitle and was hoping to use something along the lines of if worksheets(myshrttitle) exists/ is not null then worksheets(myshrttitle).delete end if thanks -- cereldine ------------------------------------------------------------------------ cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069 View this thread: http://www.excelforum.com/showthread...hreadid=561120 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If worksheet exists delete
Dim wrksht as Worksheet
set wrksht = Sheets(myshrttitle) if not wrksht is nothing then wrksht.delete end if try that. I have'nt had time to test it so it might take a little massaging to make it work. HTH Die_Another_Day cereldine wrote: I've rcorded some code that loops through a table and creates a chart on each column, the chart worksheet is named after a variable myshrttitle which is driven by a cell value. The code works fine first time and creates all the charts, what i want to achieve is when the code runs next time to delete the worksheet with name myshrttitle and then recreate it? Is this the correct way to go about the problem? I name the sheet with the following ActiveChart.Location xlLocationAsNewSheet, myshrttitle and was hoping to use something along the lines of if worksheets(myshrttitle) exists/ is not null then worksheets(myshrttitle).delete end if thanks -- cereldine ------------------------------------------------------------------------ cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069 View this thread: http://www.excelforum.com/showthread...hreadid=561120 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If worksheet exists delete
I got a chance to try it and here is the extra's you need to make it work:
Sub test() Dim myshrttitle As String Dim wrksht As Worksheet myshrttitle = "Sheet4" On Error Resume Next Application.DisplayAlerts = False Set wrksht = Sheets(myshrttitle) If Not wrksht Is Nothing Then wrksht.Delete End If On Error GoTo 0 Application.DisplayAlerts = True End Sub HTH Die_Another_Day "Die_Another_Day" wrote in message oups.com... Dim wrksht as Worksheet set wrksht = Sheets(myshrttitle) if not wrksht is nothing then wrksht.delete end if try that. I have'nt had time to test it so it might take a little massaging to make it work. HTH Die_Another_Day cereldine wrote: I've rcorded some code that loops through a table and creates a chart on each column, the chart worksheet is named after a variable myshrttitle which is driven by a cell value. The code works fine first time and creates all the charts, what i want to achieve is when the code runs next time to delete the worksheet with name myshrttitle and then recreate it? Is this the correct way to go about the problem? I name the sheet with the following ActiveChart.Location xlLocationAsNewSheet, myshrttitle and was hoping to use something along the lines of if worksheets(myshrttitle) exists/ is not null then worksheets(myshrttitle).delete end if thanks -- cereldine ------------------------------------------------------------------------ cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069 View this thread: http://www.excelforum.com/showthread...hreadid=561120 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If worksheet exists delete
Thanks, the code is getting stuck on the set wrksht sheets(myshrtttitle) part? It brings up the error 13 type mismatch. Would this hav something to do with the fact myshrttitle is a string? I tried worksheets(myshrttitle) and this brought up error 9 subscrip out of range. Also tried using on error resume next and on error goto 0. this has no had the desired effect either. Any further help appreciate -- cereldin ----------------------------------------------------------------------- cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206 View this thread: http://www.excelforum.com/showthread.php?threadid=56112 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If worksheet exists delete
How about simply
On Error Resume Next Application.DisplayAlerts = False Sheets(myshrttitle).Delete Application.DisplayAlerts = True "Die_Another_Day" wrote: I got a chance to try it and here is the extra's you need to make it work: Sub test() Dim myshrttitle As String Dim wrksht As Worksheet myshrttitle = "Sheet4" On Error Resume Next Application.DisplayAlerts = False Set wrksht = Sheets(myshrttitle) If Not wrksht Is Nothing Then wrksht.Delete End If On Error GoTo 0 Application.DisplayAlerts = True End Sub HTH Die_Another_Day "Die_Another_Day" wrote in message oups.com... Dim wrksht as Worksheet set wrksht = Sheets(myshrttitle) if not wrksht is nothing then wrksht.delete end if try that. I have'nt had time to test it so it might take a little massaging to make it work. HTH Die_Another_Day cereldine wrote: I've rcorded some code that loops through a table and creates a chart on each column, the chart worksheet is named after a variable myshrttitle which is driven by a cell value. The code works fine first time and creates all the charts, what i want to achieve is when the code runs next time to delete the worksheet with name myshrttitle and then recreate it? Is this the correct way to go about the problem? I name the sheet with the following ActiveChart.Location xlLocationAsNewSheet, myshrttitle and was hoping to use something along the lines of if worksheets(myshrttitle) exists/ is not null then worksheets(myshrttitle).delete end if thanks -- cereldine ------------------------------------------------------------------------ cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069 View this thread: http://www.excelforum.com/showthread...hreadid=561120 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet exists or not | Excel Programming | |||
Worksheet exists | Excel Programming | |||
How to check if a worksheet exists in worksheet collection | Excel Programming | |||
Worksheet Exists... | Excel Programming | |||
How can I tell if a worksheet exists? | Excel Programming |