Need assistance opening new monthly ws.

[vanoskyjoe]

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
Sheets("job held Data Entry").Select
Cells(1, 1).Select
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 = ws("FEB2008")                      <----- me trying to correct problem but error!!
newSheet = Format(Date, "mmmyyyy")

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

Range("A3:M300").ClearContents

End Sub

 

[xld]

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
    Sheets("job held Data Entry").Select
    Cells(1, 1).Select
    Application.ScreenUpdating = True

    oldSheet = "FEB2008"
    newSheet = Format(Date, "mmmyyyy")
    
    If oldSheet <> newSheet Then
        If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbYes Then
            If WorksheetExists(newSheet) Then
                MsgBox newSheet & " already exists"
                Exit Sub
            Else
                ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = newSheet
            End If
        End If
    Else
        MsgBox "month has already been created"
        Exit Sub
    End If
    
    Range("A3:M300").ClearContents

End Sub

'-----------------------------------------------------------------
Function WorksheetExists(Sh As String, _
                     Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
    If wb Is Nothing Then Set wb = ActiveWorkbook
    On Error Resume Next
    WorksheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
    On Error GoTo 0
End Function

 

[DonkeyOte]

isn't this:

If Day(Date) = 1 Then Exit Sub

closing the routine on the first of the month or am I missing something ?

 

[vanoskyjoe]

my first worsheet just has macros on it to allow the user to access the user forms.

the second worksheet is called feb2008.

but everytime I try to copy to create a new month it copies the first worksheet not feb2008.

in fact I just tried the code above and it did the same thing to me.

 

[DonkeyOte]

You're copying the ActiveSheet which is not set to be OldSheet.

 

[vanoskyjoe]

Yes I see that but everything I try is not working any suggestions?

 

[DonkeyOte]

change ActiveSheet.Copy

to

Sheets(oldsheet).Copy

I still don't understand the if day(date) = 1 THEN EXIT SUB as this would imply that on the 1st it doesn't fire but every other day it does...

also if this did only fire once a month you could determine oldsheet by using

oldSheet = Format(dateadd("m",-1,date),"mmmyyyy")

 

[xld]

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
    Sheets("job held Data Entry").Select
    Cells(1, 1).Select
    Application.ScreenUpdating = True

    oldSheet = "FEB2008"
    newSheet = Format(Date, "mmmyyyy")
    If Not WorksheetExists(newSheet) Then
    
        If oldSheet <> newSheet Then
            If MsgBox("Do you want to copy to the new month?", vbYesNo) = vbYes Then
                Worksheets(oldSheet).Copy After:=Worksheets(Worksheets.Count)
                Worksheets(oldSheet).Name = newSheet
            End If
        Else
            MsgBox "month has already been created"
            Exit Sub
        End If
        
        Range("A3:M300").ClearContents
    End If

End Sub

'-----------------------------------------------------------------
Function WorksheetExists(Sh As String, _
                     Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
    If wb Is Nothing Then Set wb = ActiveWorkbook
    On Error Resume Next
    WorksheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
    On Error GoTo 0
End Function

 

[vanoskyjoe]

ok that new code worked but the name of the new sheet was Feb2008, I already have that one I need the current month