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

    Create Sheets Macro glitch (Excel 97)

    In the attached spreadsheet (which I am sure you are all sick of by now) I have included a macro to create copies of the Template worksheet for 13 months. I have used this in another Time keeping spreadsheet where it creates a worksheet for fortnightly pay periods and it works extremely well. Including this macro means that anyone can set up a new file without knowing any coding. Best of all they are not dependant on me to set up a new one each year.

    My problem is that when I run the create sheets macro I cannot get it to:

    1. update the date in A1 for each monthly worksheet (should match the worksheet tabs)
    2. Even though I put in a date of 2005 the worksheets are created starting in 2004

    I am sure it is right under my nose but I can

  2. #2
    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: Create Sheets Macro glitch (Excel 97)

    1) remove the comment from the line:
    oNewSheet.Range("A1").Value = datDate

    so the new date is added

    2) I don't understand, mine were all created with the year I entered in the date.

    Steve

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

    Re: Create Sheets Macro glitch (Excel 97)

    1. You must uncomment the line

    'oNewSheet.Range("A1").Value = datDate

    i.e. remove the apostrophe in front of it. As it is now, this instruction is skipped.

    2. When you click the button on the setup sheet, you must enter a complete date, for example if you want to start in March, 2005, you must enter 3/1/05, not Mar-05 (for Mar-05 will be interpreted as March 5 in the current year, i.e. 3/5/04). Added: I used US date format here; use your local date format when entering the date.

    3. One other point: not every month has 31 days, so I would replace the line

    datDate = datDate + 31

    by

    datDate = DateAdd("m", 1, datDate)

    This adds exactly 1 month to the date, regardless of the number of days in that month.

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

    Re: Create Sheets Macro glitch (Excel 97)

    Thanks Hans. It works!!


    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Kerry

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

    Re: Create Sheets Macro glitch (Excel 97)

    Does this fix your problems?
    Legare Coleman

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

    Re: Create Sheets Macro glitch (Excel 97)

    Hans

    I probably should post that as a separate question. I hadn't thought of this before but on the sheets if a month is short (February for an example) is there a way to make only the 28 days show? I guess this would be some sort of date formula.

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

    Re: Create Sheets Macro glitch (Excel 97)

    Yes it does - Thank you Legare

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

    Re: Create Sheets Macro glitch (Excel 97)

    I also fixed up a couple of other things that could have caused some problems, including the things that Hans mentioned.
    Legare Coleman

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

    Re: Create Sheets Macro glitch (Excel 97)

    I appreciate that Legare. I will use your version and have noted Hans recommendation for the date format. I have put a note in input box stating the format should be 1/1/05.

    Its all a learning experience for me. You guys are fantastic.

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

    Re: Create Sheets Macro glitch (Excel 97)

    One other point: cell D4 in the template sheet should contain the formula =D2.

    To delete superfluous dates at the end of the month:
    - Declare an extra variable at the beginning of CreateSheets:

    Dim lngDays As Long

    and add the following code in the For Next loop, after strPrevSheet = oNewSheet.Name:

    lngDays = Day(DateSerial(Year(datDate), Month(datDate) + 1, 0))
    If lngDays < 31 Then
    oNewSheet.Range(oNewSheet.Cells(1, lngDays + 4), oNewSheet.Cells(1, 34)).EntireColumn.Delete
    End If

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

    Re: Create Sheets Macro glitch (Excel 97)

    Excellent Thankyou!

Posting Permissions

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