I'm working  on a program  that will take a main  page of data  and
seperate it into individual  named worksheets  according to a main
attribute.

Then the program will perform  a variety of tasks on each worksheet
(including creating  a named  range on each sheet  depending on the
number of lines  of data).

I will be updating  the main data page  periodically and wish to have
the program do all the subsequent dirtywork. The updates  may add  or
delete categories so my worksheets and graphs  can't reference  static
cell blocks.

When I attempt to use the program on updated  material, I must delete
all previous  pages and named ranges  to avoid  creating a page or range
with a duplicate  title.

I'm hoping there's a simple  command that would perform this function:

If worksheet(name) exists, then delete  worksheet.

A simple "worksheet(name).delete" command  will give  me an error  if
there is no worksheet  with that name. Likewise, "worksheet(name).add"
causes a duplication if the old worksheet hasn't been deleted.

I need to remember the "On Error Resume Next" for future programs.
I'm sure it will come in handy.

I need to remember the "On Error Resume Next" for future programs.
I'm sure it will come in handy.

Dim wSheet As Worksheet



On Error Resume Next

Set wSheet = Sheets("Sheet1")

If wSheet Is Nothing Then 'Doesn't exist

MsgBox "Worksheet does not exist", _

vbCritical,"OzGrid.com"

Set wSheet = Nothing

On Error GoTo 0

Else 'Does exist

MsgBox "Sheet 1 does exist", _

vbInformation,"OzGrid.com"

Set wSheet = Nothing

On Error GoTo 0

End If



There is more at: http://www.ozgrid.com/VBA/IsWorkbookOpen.htm

If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0

it is possible to loop through your sheets.
I would suggest doing just that because, as you mentioned, you may be
dropping some categories. In which case you don't know the name
of the category you removed!

here's what I'd do:


For I = 1 To Sheets.Count
If (sheets(I).name <> "KEEPthisSHEET") Then
Application.DisplayAlerts = False
Sheets(I).Delete
Application.DisplayAlerts = True
End If
Next I

I use it frequently.

Here's an interesting tip:
The "reverse" of: On Error Resume Next
is: On Error goto 0

I know, doesn't make any sense, but that's the way it is!

Here's another thought/variation: why delete  the sheets when you
can just change the part that has changed? you can check  to see if
a sheet  exists with this code:

On Error Resume Next
Set wSheet = Sheets(tmsheets)
'<----tmsheet is the variable for the sheet name
If wSheet Is Nothing Then 'sheet Doesn't exist
'<---create the sheet or whatever you want to do
Set wSheet = Nothing
'<---reset the variable for the next time it's used
Else 'sheet Does exist
'<---do what you want to do with the sheet
End If

I am working  on a program  that essentially does the same thing
(create sheets of particular team categories from a main  data
sheet), and have it set up to change the data  on the main data sheet
whenever I change something on one of the team sheets. I found it
was much faster, when I did a Find match for the identifing line
code and just change that line, rather than erase the whole thing.
I also have it reset the named  ranges when a new set of data is
being evaluated.

But, your situation and need might be a lot different, so that's
fine. Just thought I'd toss out those ideas.