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:
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