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

    New monthly ws (excel/vba)

    i have tried many way and times before in the past trying to achieve this process. The process of automatically creating a new monthly worksheet each and every 1st of the month. time and time I have failed. My manager is really getting upset by this. can anybody assist me on this ASAP today. PLEASE!!!

    The problem is that it copies my from page with macros into a new month. What I need to have happen is to copy the worksheet named FEB2008, rename it to the new month in this case JUL2008 and clear out all the cells int he JUL2008 spreedsheet.

    Again I'm pleading somevody please help with this .

    here is my current code:
    Private Sub Workbook_Open()

    Dim strValue As String
    Dim Sht As Worksheets, foundSheet As Boolean, TEMPLATE As String
    Dim rng As Range
    Dim iRow As Long
    Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
    Dim ws As Worksheet
    Dim strName As String

    Application.ScreenUpdating = False
    Set ws = Sheets("oneshotdataentry")
    Application.ScreenUpdating = True

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

    oldSheet = ActiveSheet.Name
    newSheet = Format(Date, "mmmyyyy")

    If oldSheet = newSheet Then
    If WorksheetExists(newSheet) Then
    MsgBox newSheet & " already exists"
    Exit Sub
    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = newSheet
    End If
    MsgBox "month has already been created"
    Exit Sub
    End If


    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    If ws.Cells(iRow, 9).Value = "n/a" Then
    ws.Cells(iRow, 9).Value = ""
    End If

    If ws.Cells(iRow, 9).Value = "na" Then
    ws.Cells(iRow, 9).Value = ""
    End If
    If ws.Cells(iRow, 9).Value = "N/A" Then
    ws.Cells(iRow, 9).Value = ""
    End If

    If ws.Cells(iRow, 9).Value = "NA" Then
    ws.Cells(iRow, 9).Value = ""
    End If

    End Sub


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: New monthly ws (excel/vba)

    Try this:

    Private Sub Workbook_Open()
    Dim strSheet As String
    Dim wsh As Worksheet
    strSheet = Format(Date, "mmmyyyy")
    On Error Resume Next
    ' Try to set a reference to the month sheet
    Set wsh = Worksheets(strSheet)
    On Error GoTo 0
    If wsh Is Nothing Then
    ' Sheet doesn't exist yet, so ask the user
    If MsgBox("Do you want to create a sheet for this month?", vbYesNo) = vbNo Then
    Exit Sub
    End If
    ' Copy the template sheet
    Worksheets("Feb2008").Copy After:=Worksheets(Worksheets.Count)
    ' Set reference to new sheet
    Set wsh = Worksheets(Worksheets.Count)
    ' Rename it
    wsh.Name = strSheet
    ' And clear some cells
    ' More code here
    ' ...
    End If
    End Sub

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

    Re: New monthly ws (excel/vba)

    I am a little confused by your code. If you want to copy FEB2008 why are you activating "oneshotdataentry"?

    Which cells iin the newly created worksheet do you want cleared? Just Range("A3:M300")?

    What is the purpose of the last steps and converting various NAs to nullstrings? Do you want to clear those cells as well? You are changing them in "oneshotdataentry" whether it is the first day of the month or not, but they will still exist in the copy you made. Is this what you want?

    Note also that if the first of the month is on a weekend or holiday or the file is just not opened on the first day of the month, the sheet is not automatically created. Is this what you want to ONLY add a new sheet on the first day of the month?

    [I presume WorksheetExists is a function you have created and is in this workbook and results in TRUE/FALSE depending on whether the name exists or not]

    Please detail exactly what you want the code to do...


Posting Permissions

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