Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Is there a simple command that can check if a worksheet exists?

  Asked By: Abelerd    Date: Jan 24    Category: MS Office    Views: 920
  

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.

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Roop Kapoor     Answered On: Jan 24

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.

 
Answer #2    Answered By: Abasi Massri     Answered On: Jan 24

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

 
Answer #3    Answered By: Aylin Kaya     Answered On: Jan 24

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

 
Answer #4    Answered By: Rhys Evans     Answered On: Jan 24

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

 
Answer #5    Answered By: Mildred Bailey     Answered On: Jan 24

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

 
Answer #6    Answered By: Lee Butler     Answered On: Jan 24

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.

 
Answer #7    Answered By: Jennifer Davis     Answered On: Jan 24

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!

 
Answer #8    Answered By: Beaudi Smith     Answered On: Jan 24

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
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.

 




Tagged: