Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    new spreadsheet/newmonth (vba/excel)

    have tried now for four months, and I am still unable to get my spreadsheets to open a new spreadsheet for the new month, (Ie... april to may, etc..)

    I have tried many ways and suggestions from others still no avail.

    I need somebody who can help me

    PLEASE!!!!

    thank you

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

    Re: new spreadsheet/newmonth (vba/excel)

    Woody's Lounge is not a software development company, we can't create an application for you. If you ask a specific question, we may be able to help you. "I have tried many ways and suggestions from others still no avail" is too vague, sorry.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    I need to be able to have my excel spreadsheet (s) on the first of every month be able to recognize that it is the first of the month and to create a new spreadsheet named after that month.

    if it's May 1st then I need it to creat a spreadsheet called May2008, but I have tried this many a ways and still can't get it to creat a new spreadsheet for the new month.

    <code>
    If Day(Date) = 1 Then Exit Sub
    If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbNo Then Exit Sub
    newDate = DateSerial(Year(Date), Month(Date), 1)
    newSheet = Format(newDate, "mmmyyyy")
    </code>

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

    Re: new spreadsheet/newmonth (vba/excel)

    If you want to create a new sheet within the current workbook, you'd need to add a line

    Worksheets.Add.Name = newSheet

  5. #5
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    ok i will try that

  6. #6
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    I have tried it that way, and it opened a new spreadsheet call JAN1900 instead of JUN2008.

    this is the code I currently have:

    <code>
    Private Sub Workbook_Open()
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String

    If Day(Date) = 1 Then Exit Sub

    If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbNo Then Exit Sub
    newDate = DateSerial(Year(myDate), Month(myDate) + 1, 1)
    newSheet = Format(newDate, "mmmyyyy")
    Worksheets.Add.Name = newSheet

    End Sub
    </code>

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

    Re: new spreadsheet/newmonth (vba/excel)

    That's because you don't assign a value to myDate. I'd do it differently:

    Private Sub Workbook_Open()
    Dim strName As String
    Dim wsh As Worksheet
    strName = Format(DateAdd("m", 1, Date), "mmmyyyy")
    On Error Resume Next
    Set wsh = Worksheets(strName)
    If wsh Is Nothing Then
    Set wsh = Worksheets.Add
    wsh.Name = strName
    End If
    wsh.Activate
    End Sub

  8. #8
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    Hans that definately did the trick, but two more small questions for you. If I want to copy the previous sheet and then clear the contents of the new sheet would it be something like this:

    <code>
    For Each Sht In Worksheets
    If Sht.Name = newSheet Then
    MsgBox "This Monthly Sheet has already been opened"
    foundSheet = True
    Sht.Activate
    Exit For
    End If
    Next
    If Not foundSheet Then
    ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = newSheet
    Range("A3:I3000").ClearContents
    End If
    </code>

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

    Re: new spreadsheet/newmonth (vba/excel)

    Yes.

  10. #10
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    Hans, I'm having a problem... It creates a new spreadsheet but it does not copy the previous months. I have column headers in the spreadsheet and they are not being copied over.

    Any suggestions

    <code>
    if Day(Date) = 1 Then Exit Sub
    If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbNo Then Exit Sub

    strName = Format(DateAdd("m", 1, Date), "mmmyyyy")
    On Error Resume Next
    Set wsh = Worksheets(strName)
    If wsh Is Nothing Then
    Set wsh = Worksheets.Add
    wsh.Name = strName
    End If
    wsh.Activate

    For Each Sht In Worksheets
    If wsh.Name = newSheet Then
    MsgBox "This Monthly Sheet has already been opened"
    foundSheet = True
    wsh.Activate
    Exit For
    End If
    Next
    If Not foundSheet Then
    ActiveSheet.Name = newSheet
    Range("A3:I3000").ClearContents
    End If

    End Sub
    </code>

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

    Re: new spreadsheet/newmonth (vba/excel)

    You're mixing up different bits of code. Please use either one or the other, not bits of both.

  12. #12
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    ok, so then how do I go about correcting it so that it copies the previous months spreadsheet into the new month? thats all that I'm getting hung up on now

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

    Re: new spreadsheet/newmonth (vba/excel)

    Try this:
    <code>
    Private Sub Workbook_Open()
    Dim strOld As String
    Dim strNew As String
    Dim wshOld As Worksheet
    Dim wshNew As Worksheet
    strNew = Format(DateAdd("m", 1, Date), "mmmyyyy")
    On Error Resume Next
    Set wshNew = Worksheets(strNew)
    If wshNew Is Nothing Then
    strOld = Format(Date, "mmmyyyy")
    Set wshOld = Worksheets(strOld)
    wshOld.Copy After:=Worksheets(Worksheets.Count)
    Set wshNew = Worksheets(Worksheets.Count)
    wshNew.Name = strNew
    wshNew.Range("A3:I3000").ClearContents
    End If
    wshNew.Activate
    End Sub
    </code>
    This assumes that the workbook already contains a worksheet for the current month (currently May2008). It checks whether there is a sheet for next month (Jun2008) and if not, it copies the sheet for the current month and clears A3:I3000 in the new sheet.

  14. #14
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    Thank you so Much Sir. That is it... I really do appreciate it.

    bye for now

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new spreadsheet/newmonth (vba/excel)

    I did this in MS-DOS years ago:
    <pre>call d:batlapsetdate.bat
    if exist g:GreavesAdmin%yr%%YR%_%MT%.xls goto GotFile
    copy g:GreavesAdminExpense.xls g:GreavesAdmin%yr%%YR%_%MT%.xls
    :GotFile
    copy g:GreavesAdmin%yr%%YR%_%MT%.xls g:GreavesAdminExpense.xls
    ::
    g:GreavesAdmin%yr%%YR%_%MT%.xls
    </pre>


    The call to SetDate sets two environment variables, /yr/ and /mt/
    If this month's workbook (or document or presentation etc.) is found, it is copied to the generic workbook.
    Otherwise the generic workbook is copied to the current month.
    This technique has a byproduct of making an extra backup copy of my expense sheets before going into my late-at-night book-keeping ploy.

Posting Permissions

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