Results 1 to 4 of 4
  1. #1
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Creating a calendar template or macro (Excel 2010)

    Please see the attached file. I have seen a lot of sample Excel calendars, but they are too nice. In this case, my colleagues want something very simple. I need some help to automate the process of generating a calendar each month. Could someone help me out, please?

    Thanks very much,

    Jim
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    You could make A4: =1+A3
    and fill that down. Then, entering the first of each month in A2 will make the other dates automatic.

    You could also make B3: =TEXT(A3,"ddd") and fill down and you'll generate the day of the week.

    If column B must be 2-letter uppercase, fill down: =UPPER(LEFT(TEXT(A3,"ddd"),2))

    Is that the not to nice you're looking for?
    Last edited by kweaver; 2016-01-12 at 20:19.

  3. #3
    Silver Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    2,072
    Thanks
    0
    Thanked 259 Times in 248 Posts
    I've pointed several clients to www.office.com to get Templates that can be edited. A lot of the 'fancies' can be deleted

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Jim,

    Here is some code that will create the next month's calendar. Clicking the "Create New Month" button will instantly append it as the next sheet and rename it with the date and year.

    HTH,
    Maud

    Code:
    Sub CreateCalendarSheet()
    '------------------------------
    'DECLARE AND SET VARIABLES
    Dim dte As Date, firstday As Date, lastday As Date
    Dim numdays As Integer, I As Integer, J As Integer
    '------------------------------
    'CREATE NEW WORKSHEET TO THE END
    With Worksheets
        .Add After:=Worksheets(.Count)
    '------------------------------
    'CREATE CALENDAR DATES AND DAYS
        With Worksheets(.Count - 1)
            dte = .Range("A3")
            firstday = DateSerial(Year(dte), Month(dte) + 1, 1)
            lastday = DateSerial(Year(dte), Month(dte) + 2, 0)
            numdays = lastday - firstday + 1
            For I = 3 To numdays + 2
                Cells(I, 1) = DateSerial(Year(dte), Month(dte) + 1, I - 2)
                Cells(I, 2) = UCase(WeekdayName(Weekday(Cells(I, 1), vbSunday), True, vbSunday))
            Next I
    '------------------------------
    'RENAME SHEET
            ActiveSheet.Name = MonthName(Month(dte) + 1, True) & "_" & Year(dte)
    '------------------------------
    'fORMAT NEW SHEET
            For J = 1 To 7
                Cells(2, J + 2) = "Pharmacist" & J
            Next J
            ActiveSheet.Columns("C:I").AutoFit
        End With
    End With
    End Sub
    Attached Files Attached Files

Posting Permissions

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