If worksheet exists delete

[cereldine]

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

[Die_Another_Day]

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

[Die_Another_Day]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[cereldine]

Thanks, the code is getting stuck on the set wrksht = sheets(myshrtttitle)
part? It brings up the error 13 type mismatch. Would this have something to do with the fact myshrttitle is a string?

I tried worksheets(myshrttitle) and this brought up error 9 subscript out of range.
Also tried using on error resume next and on error goto 0. this has not had the desired effect either. Any further help appreciated

[Charlie]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>