Start a new worksheet per month

[stingerj]

all my code is listed below. for all ws's, forms an modules. I need to know how to be able to open up a new workbook once the month changes. so that all of february is with february and all of march is in march etc...


[CODE=vb]Option Explicit

Private Sub cmdRJFH_Click()
Dim iRow As Long
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Set ws = Worksheets("feb 2008")


'check for a job name!
If Trim(Me.txtRLJn ame.Value) = "" Then
Me.txtRLJname.S etFocus
MsgBox "Please enter the Job name that has been requested to be taken off hold!"
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 9).Value = Me.txtRLJname.V alue
ws.Cells(iRow, 10).Value = Me.txtInitials. Value
If Me.txtInitials. Value = "" Then
Me.txtInitials. SetFocus
MsgBox "Please enter your initials as requested!"
Exit Sub
ws.Cells(iRow, 11).Value = Me.txtNop2.Valu e
If Me.txtNop2.Valu e = "" Then
Me.txtNop2.SetF ocus
MsgBox "Please enter the Requestor's Name!"
Exit Sub
ws.Cells(iRow, 12).Value = Now
ws.Cells(iRow, 13).Value = Me.txtCOMments. Value

'clear the data [/B]
Me.txtRLJname.V alue = ""
Me.txtInitials. Value = ""
Me.txtNop2.Valu e = ""
Me.txtCOMments. Value = ""

Me.txtRLJname.S etFocus
End Sub

Private Sub cmdCLEar_Click( )
'clear the data
Me.txtRLJname.V alue = ""
Me.txtInitials. Value = ""
Me.txtNop2.Valu e = ""
Me.txtCOMments. Value = ""

Me.txtRLJname.S etFocus
End If

End Sub

Private Sub cmdClose_Click( )
Unload Me
End Sub

Private Sub UserForm_QueryC lose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMe nu Then
Cancel = True
MsgBox "Please use the exit button!"
End If
End Sub[/CODE]


This is the code for the workbook:
[CODE=vb]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
oldSheet = ActiveSheet.Nam e
myDate = DateValue("1-" & oldSheet)
newDate = DateSerial(Year (myDate), Month(myDate) + 1, 1)
newSheet = Format(newDate, "mmmyyyy")
ActiveSheet.Cop y After:=Workshee ts(Worksheets.C ount)
ActiveSheet.Nam e = newSheet
Range("A3:M300" ).ClearContents

End Sub[/CODE]


And this is the code from userform1

[CODE=vb]Option Explicit

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Set ws = Worksheets("feb 2008")
'Set ws = ActiveSheet.Nam e
'find first empty row in database
iRow = ws.Cells(Rows.C ount, 1) _
.End(xlUp).Offs et(1, 0).Row

'check for a job name!
If Trim(Me.txtJobn ames.Value) = "" Then
Me.txtJobnames. SetFocus
MsgBox "Please enter the Job name that has been requested to be placed on hold!"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Date
ws.Cells(iRow, 2).Value = Me.txtJobnames. Value
ws.Cells(iRow, 3).Value = Me.txtSdnumber. Value
If Me.txtSdnumber = "" Then
Me.txtSdnumber. SetFocus
MsgBox "Please enter the Service Desk Number!"
Exit Sub
End If
If IsNumeric(ws.Ce lls(iRow, 3).Value) Then
ws.Cells(iRow, 4).Value = Me.txtRequestor .Value
End If
If Me.txtRequestor .Value = "" Then
Me.txtRequestor .SetFocus
MsgBox "Please enter the Requestor's Name!"
Exit Sub

End If
ws.Cells(iRow, 5).Value = Me.txtUhold.Val ue
ws.Cells(iRow, 6).Value = Me.txtINstructi ons.Value
ws.Cells(iRow, 7).Value = Me.txtInitials. Value
If Me.txtInitials. Value = "" Then
Me.txtInitials. SetFocus
MsgBox "Please enter your initials as requested!"
Exit Sub
End If
ws.Cells(iRow, 8).Value = Me.txtCOmment.V alue


'clear the data

Me.txtJobnames. Value = ""
Me.txtSdnumber. Value = ""
Me.txtRequestor .Value = ""
Me.txtUhold.Val ue = ""
Me.txtINstructi ons.Value = ""
Me.txtInitials. Value = ""
Me.txtCOmment.V alue = ""
Me.txtJobnames. SetFocus

End Sub

Private Sub cmdCLEar_Click( )
'clear the data

Me.txtJobnames. Value = ""
Me.txtSdnumber. Value = ""
Me.txtRequestor .Value = ""
Me.txtUhold.Val ue = ""
Me.txtINstructi ons.Value = ""
Me.txtInitials. Value = ""
Me.txtCOmment.V alue = ""
Me.txtJobnames. SetFocus
End Sub

Private Sub cmdClose_Click( )Unload Me
End Sub

Private Sub UserForm_QueryC lose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMe nu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
[/CODE]


please help thanks

[kadghar]

WOW!!

thats a huge bunch of code ^.^

well, i'll give you some tips, if they dont help you, then i'll read it =(

use smart worksheet names that VB can read as dates. eg "February 2008" is a great name (unles your excel is in other language where, for example, "Febrero 2008" works great in spanish)

then, i'll assume (yeah, that word makes magic) that you have the date somewhere in some variable (I'll call it myDate). Use a for to check if that date's month and year is already in a sheet, then select that worksheet, else, create a new one.

[CODE=vb]dim i as integer
dim Dat1 as date
for i = 1 to worksheets.coun t
dat1=worksheets (i).name
if month(dat1) = month(mydate) and year(dat1) = year(mydate) then exit for
next
if i > worksheets.coun t then
worksheets.add
else
worksheets(i).s elect
end if[/CODE]

if you dont have such a variable (myDate) and you were talking about the date when you run the code, then use the systems date, so replace myDate with Date.

Hope that helps (i really do!)