Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Naming Tabs for Months (Excel 2000)

    Hello there

    I found this macro to create sheets for weeks which is great. How can it be modified to make Tabs for months instead of weeks? In this format for 12 months.

    Apr 03
    May 03
    Jun 03
    Jul 03

    Sub YearWorkbook()
    Dim iWeek As Integer
    Dim sht As Variant
    Dim sTemp As String
    Dim dSDate As Date

    sTemp = InputBox("Date for the first worksheet:", "End of Week?")
    dSDate = CDate(sTemp)

    Application.ScreenUpdating = False
    Worksheets.Add After:=Worksheets(Worksheets.Count), _
    Count:=(52 - Worksheets.Count)
    For Each sht In Worksheets
    sht.Name = Format(dSDate, "dd-mmm-yyyy")
    dSDate = dSDate + 7
    Next sht
    Application.ScreenUpdating = True
    End Sub


    Regards Kerry

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Naming Tabs for Months (Excel 2000)

    Try this,

    Sub YearWorkbookByMonth()
    Dim i as Integer
    Worksheets.Add After:=Worksheets(Worksheets.Count), _
    Count:=(12 - Worksheets.Count)
    For i = 1 To 12
    Sheets(i).Name = Format(DateSerial(Year(Now), i 1), "mmm yy")
    Next
    End Sub


    Andrew C

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Naming Tabs for Months (Excel 2000)

    Hi Andrew

    Something is going wrong in this line ?

    Sheets(i).Name = Format(DateSerial(Year(Now), i 1), "mmm yy")

    Kerry

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

    Re: Naming Tabs for Months (Excel 2000)

    There is a comma missing. It should be:

    <pre> Sheets(i).Name = Format(DateSerial(Year(Now), i, 1), "mmm yy")
    </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
  •