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

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

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

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

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