Need assistance opening new monthly ws.

vanoskyjoe

Board Regular
Joined
Oct 24, 2007
Messages
107
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:

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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
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

    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
 
Last edited:
Upvote 0

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
isn't this:

Code:
If Day(Date) = 1 Then Exit Sub

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

vanoskyjoe

Board Regular
Joined
Oct 24, 2007
Messages
107
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.
 
Upvote 0

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
You're copying the ActiveSheet which is not set to be OldSheet.
 
Upvote 0

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,124
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")
 
Last edited:
Upvote 0

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
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

    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
 
Upvote 0

vanoskyjoe

Board Regular
Joined
Oct 24, 2007
Messages
107
ok that new code worked but the name of the new sheet was Feb2008, I already have that one I need the current month
 
Upvote 0

Forum statistics

Threads
1,191,690
Messages
5,988,061
Members
440,125
Latest member
vincentchu2369

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top