Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro to change name of sheet tab (97)

    I create a worksheet for our timesheets each week. I get a macro to copy the master and paste it at the end. XL names it master(2).
    I then rename it to say 140703 (eg 14 July 03). The previous ws I will have renamed to 070703.

    What code do i need to add to my macro so that xl will auto rename the sheet for me.
    In effect I want the macro to rename the sheet from master(2) to whatever the previous sheet was called with 7 added to the 1st 2 digits. (so if the prev was 070703, I want XL to recognize that, and make this week's 140703).

    I know that if the previous week's sheet was named say 300703, then adding 7 will not give the correct date. But changing it manually once a month is no trouble, and hardly justifies the coding that'd be need to get around this minor prob.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to change name of sheet tab (97)

    I would reserve a fixed cell for the most recently used date. It can be in an existing worksheet, or you could create a hidden worksheet for it.
    Let's say that you use cell A1 in a sheet named Master for this purpose. In your example it would contain 7 July 2003.
    The code would add 1 week to the contents of this cell, then rename Master(2) to the new date, formatted as ddmmyy. The advantage of using a date is that adding 7 will work correctly across months and years.

    Sub RenameMaster2()
    Dim oCell As Range
    Set oCell = Worksheets("Master").Range("A1")
    oCell = oCell + 7
    Worksheets("Master(2)").Name = Format(oCell, "mmddyy")
    Set oCell = Nothing
    End Sub

    Adapt as needed.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro to change name of sheet tab (97)

    Does this do what you want?

    It copies master to the end and then names it TODAY's date formatted as ddmmyy. This is much easier than going 7 days past a different sheet.

    You will get a runtime error if you run it twice on the same day, since the sheetnames can not be duplicate.

    Steve

    <pre>Sub CopyMasterToday()
    Sheets("Master").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Format(Now, "ddmmyy")
    End Sub</pre>


    Steve

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

    Re: macro to change name of sheet tab (97)

    Does this do what you asked?

    <pre>Dim strName As String, datWk As Date
    strName = Worksheets(Worksheets.Count).Name
    datWk = DateSerial(Right(strName, 2), Mid(strName, 3, 2), Left(strName, 2)) + 7
    Worksheets("Master").Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = Format(datWk, "ddmmyy")
    </pre>

    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: macro to change name of sheet tab (97)

    A small warning. We had a workbook that used sheets with names that were completely numeric.

    If your Macro ever attempts to access the sheet using the syntax ActiveWorkbook.Worksheets("txtSheetName") then it causes errors because it takes the sheet name to be an index into the collection, instead of a named member of the collection.

    We worked around this by adding a text character to the beginning of the name

    StuartR

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to change name of sheet tab (97)

    Thanks guys

    Of the 3 solutions, Steve's one worked best.

    What code could I add to the macro to put the date in cell A1 and freeze it there?

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

    Re: macro to change name of sheet tab (97)

    Steve's code names the sheet with the current date, not the previous sheets date plus 7 days. Is that what you wanted? It's not what you asked.

    Try:

    <pre> ActiveSheet.Range("A1").Value = Date
    </pre>

    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to change name of sheet tab (97)

    Legare,

    With yr code I got "runtime error 13(mismatch) at line
    datWk = DateSerial(Right(strName, 2), Mid(strName, 3, 2), Left(strName, 2)) + 7

    ...which I'm sure u could debug with little trouble. but don't spend any more time on it please.

    the code for putting the date in A1 worked fine. Thanks.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro to change name of sheet tab (97)

    You will get that error if the last sheet in the workbook is NOT named with the "date" you listed (that is in the format:
    "041003" If all the chars are NOT numbers, and the function expects all numbers, you get the error.

    What is the contents of the variable strname when you get the error?

    Steve

Posting Permissions

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