+ Reply to Thread
Results 1 to 3 of 3

Check for dated sheet

  1. #1
    Mike K
    Guest

    Check for dated sheet

    Oh great ones,
    I have a recorded macro which works fine when opened
    the first time. How would I add a check for exsistance of
    this specific sheet name to avoid the error you get when
    you re-open the workbook? It will ultimately be a workbook
    open event.

    Excel 2003
    Office 2003

    Sub blank1()
    Sheets("Blank").Select
    Sheets("Blank").Copy Before:=Sheets(1)
    Sheets("Blank (2)").Select
    Worksheets("Blank (2)").Name = Format(Date - 1, "mm-dd-
    yyyy")
    End Sub

    thanks,
    Mike

  2. #2
    Registered User
    Join Date
    11-26-2003
    Posts
    8
    Well, it's a bit clumsy, but how about...


    Set wksht = Nothing

    On Error Resume Next
    Set wksht = Sheets("Sheetnametocheckfor")
    On Error GoTo 0

    If wksht Is Nothing Then
    'In other words - there exists no sheet so far with this name - so first run
    Else
    'Make provision for having sheet already there - i.e. run already once
    End If


    Obviously you'll need to change the "sheetnametocheckfor" with some formulaic expression for the sheet name.

  3. #3
    Bob Phillips
    Guest

    Re: Check for dated sheet

    Sub blank1()
    Dim oWs As Worksheet
    On Error Resume Next
    Set oWs = Sheets("Blank")
    On Error GoTo 0
    If Not oWs Is Nothing Then
    oWs.Copy Before:=Sheets(1)
    ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy")
    End If
    End Sub


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Mike K" <[email protected]> wrote in message
    news:[email protected]...
    > Oh great ones,
    > I have a recorded macro which works fine when opened
    > the first time. How would I add a check for exsistance of
    > this specific sheet name to avoid the error you get when
    > you re-open the workbook? It will ultimately be a workbook
    > open event.
    >
    > Excel 2003
    > Office 2003
    >
    > Sub blank1()
    > Sheets("Blank").Select
    > Sheets("Blank").Copy Before:=Sheets(1)
    > Sheets("Blank (2)").Select
    > Worksheets("Blank (2)").Name = Format(Date - 1, "mm-dd-
    > yyyy")
    > End Sub
    >
    > thanks,
    > Mike




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1