Results 1 to 4 of 4
  1. #1
    grettmill
    Guest

    Check if a sheet exists & if so, delete it

    I have an Excel macro which, among other things, creates & names a new sheet. I am trying to "bullet-proof" the macro now, and I want to add some code at the beginning that will check for the sheet that the macro creates, and if found, delete it. Once deleted, or if not found, it should go on and run the macro. Any ideas on how to do this? Thanks in advance.

  2. #2
    grettmill
    Guest

    Re: Check if a sheet exists & if so, delete it

    Never mind, a co worker helped me out with this. For the curious, here's what we used:

    On Error GoTo errorhandler
    Sheets("Oursheet").Select
    errorhandler:
    If Err.Number = 9 Then
    Sheet.Activate ("Sheet2")
    Sheets.Add.Name = "Oursheet"
    End If
    (rest of macro)...

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if a sheet exists & if so, delete it

    Hi,

    I'm not fond of solutions using the error handler, so if there's another way of doing something I will (the errorhandler may get activated for the wrong reason; I've sometimes had it not kicking in at all.

    So my solution would be soemthing like;

    <pre>Dim i As Integer

    For i = ActiveWorkbook.Sheets.Count To 1 Step -1
    If ActiveWorkbook.Sheets(i).Name = "OurSheet" Then
    ...
    End If
    Next
    </pre>


    which gets rid of the need for an errorhandler.

    The reason for using a "for i =" look rather than a "for each" loop, and doing it backward, is that if we are in a forward loop and delete a sheet, things may not get handled correctly.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if a sheet exists & if so, delete it

    I would do something like this:

    <pre>Dim wsOS As Worksheet

    On Error Resume Next
    Set wsOS = Worksheets("OurSheet")
    On Error GoTo 0
    If Not wsOS Is Nothing Then
    Application.DisplayAlerts = False
    wsOS.Delete
    Application.DisplayAlerts = True
    End If
    wsOS = Nothing
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •