month end needs to open new worksheet.. (excel / vba 2003)

Topic

New Reply

I need to be able to open a new worksheet in the same workbook through vba/excel that will copy the previous days workbook and blank out / clear contents on the worskheet so the player has a new sheet to work with. This only happens at the end of the month. So that there is a new sheet for Feb-2008 etc etc .. through dec-2008. I have one workbook with two worksheets, one is were the data base resides (currently called jobs held) the other worksheet just allows the user access to the userform to input the data. Please can anybody help.

This is what I have so far for this one.

Option Explicit

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(“JOBS HELD”)

‘find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

‘check for a hold date
If Trim(Me.txtHdate.Value) = “” Then
Me.txtHdate.SetFocus
MsgBox “Please enter todays date”
Exit Sub
End If

‘copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtHdate.Value
ws.Cells(iRow, 2).Value = Me.txtJobnames.Value
ws.Cells(iRow, 3).Value = Me.txtSdnumber.Value
ws.Cells(iRow, 4).Value = Me.txtUhold.Value
ws.Cells(iRow, 4).Value = Me.txtINstructions.Value
ws.Cells(iRow, 4).Value = Me.txtINitials.Value
ws.Cells(iRow, 4).Value = Me.txtRSchedule.Value
ws.Cells(iRow, 4).Value = Me.txtCOmment.Value

‘clear the data
Me.txtHdate.Value = “”
Me.txtJobnames.Value = “”
Me.txtSdnumber.Value = “”
Me.txtUhold.Value = “”
Me.txtINstructions.Value = “”
Me.txtINitials.Value = “”
Me.txtRSchedule.Value = “”
Me.txtCOmment.Value = “”
Me.txtHdate.SetFocus
MsgBox “GOOD JOB, PLEASE EXIT!!”
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox “Please use the button!”
End If
End Sub

Thank you, Also I was trying to figure out how to get color palette # 35 permanently in cells c3:c200 and g3:g200 is there a way to combine more than one column like that?

Reply | Quote

Replies

Welcome to Woody’s Lounge!

I’m confused by your question. You have a workbook with two sheets and you want to “copy the previous days workbook”.
It would be helpful if you could post a copy of your workbook with dummy data. Replace or remove any sensitive data.
The maximum size for an attachment is 100 KB but you can zip the workbook if necessary and attach the zip file.

I also don’t understand your last question. What do you mean by “is there a way to combine more than one column like that”?

Reply | Quote

hello hans long time no here….

the first worksheet is currently blank right now. What i did was create a userform called hold jobs. which allows the user to input data that will be force into the database (sheet1 or as I call it now jobs held)in their proper cells. This sheet is currently hidden from the user. the second sheet is basically a macro button that allows the userform to pop open so the data entry can begin (this sheet is currently called job helds data entry). What I need to do is at the start of every month I need to copy the previous months sheet (clearing the contents, so the users have a new sheet) and so that their input starts in cell a3 all over again. I also have the problem of changing sheet1’s name from jobs held for my code up on top has that within.

Set ws = Worksheets(“JOBS HELD”)

I hope this explains it better.

Thanks Joe Vanosky

Reply | Quote

Do you really need to create a new sheet for each month? If you need to create a yearly overview later on, it would be better to keep everything in one data sheet. You can always filter for a specific month.

If you do need to use a separate sheet for each month, I’d keep a copy of the blank sheet Jobs Held to act as template.
Name the month sheets Jan2008, Feb2008 etc.
When the user clicks cmdAdd, you can derive the sheet name from the date entered in txtHdate: it’s Format(txtHdate, “mmmyyyy”).
If the sheet exists, use it, and if it doesn’t (which will happen for a new month), copy the template sheet and rename it

Dim strSheet As String
strSheet = Format(txtHdate, “mmmyyyy”)
On Error Resume Next
‘ Try to set a reference to the month sheet
Set ws = Worksheets(strSheet)
On Error GoTo 0
If ws Is Nothing Then
‘ Sheet doesn’t exist yet, so copy the template sheet
Worksheets(“Jobs Held”).Copy After:=Worksheets(Worksheets.Count)
‘ Set reference to new sheet
Set ws = Worksheets(Worksheets.Count)
‘ And rename it
ws.Name = strSheet
End If
…

Reply | Quote

the txthdate is what the user types in. Is there a way for it to know that the month changed before they open the sheet. So say it’s say that its march 1st 2008 and the user needs to add another new job that got held today. Would that user when he/she opened it up would they still be in february’s sheet or would it automatically open to marchs sheet. Also I need to have the new sheets contents cleared except cells A1 through H2. another question is where would I put this code at?

Reply | Quote

hey hans…
I was able to get this to work but it is creating to new worksheets (one for the4 new month and another for the new jobs held data entry (user form). I would like to keep the same userform and just increas the months.

rivate Sub Workbook_Open()

Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
If MsgBox(“Do you want to copy to the new month?”, vbYesNo) = vbNo Then Exit Sub
oldSheet = ActiveSheet.Name
myDate = DateValue(“1-” & oldSheet)
newDate = DateSerial(Year(myDate), Month(myDate) + 1, 1)
newSheet = Format(newDate, “mmmyyyy”)
ActiveWorkbook.Sheets.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newSheet
Range(“A3:M300”).ClearContents

End Sub

thx Joe

Reply | Quote

The line

ActiveWorkbook.Sheets.Copy After:=Worksheets(Worksheets.Count)

copies all sheets in the workbook. Try this instead:

ActiveSheet.Copy After:=Worksheets(Worksheets.Count)

Reply | Quote

Thank you so much it works perfectly now until the user see’s it on monday.

thank you again hans,

Reply | Quote

Sorry hans

I have a couple more questions, on another workbook of mine.

the problem with this workbook is that the user needs a new worksheet for every day of the week.
but they use the same worksheet from 10pm until 10am every day.
so how would I go about doing that. I know there has to be some reference to the day, time, and because ot the new day (midnight). Any ideas?

Reply | Quote

Wouldn’t it be easier to use a sheet for a 10PM-10AM period, i.e. include the time from 10 PM to 12 midnight in the sheet for the next day?

Reply | Quote

yes, but there is no way to get around this one.
You see the users come in at 6pm and dont really open it up until aroun 10pm but the keep updating it throughout the night until 6 am. Then at 7 am someone else update it until 10 am when it gets saved and the new day begins.

Reply | Quote

You could name the sheet after the day, for example in mmmddyyyy format (Feb232008), and compare the name of the active sheet to Format(Date, “mmmddyyyy”).

If they don’t match, create a new sheet and name it using the above format.

Reply | Quote

ok let ne know if this is what you mean or not, please?
the clear contents are where the user inputs data within this sheet. but never mind that now I just need to get this compare worked out. oh yeah i also renamed the feb 22 2008.

Dim mydate As Date, newDate As Date, oldSheet As String, newSheet As String
If MsgBox(“Do you want to copy to the new day?”, vbYesNo) = vbNo Then Exit Sub
oldSheet = ActiveSheet.Name
mydate = DateValue(“1” & oldSheet)
newDate = DateSerial(Month(mydate), day(mydate) + 1, 1)
newSheet = Format(Date, “mmmddyyyy”)
ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newSheet
Range(“A7:I7”).ClearContents
With Range(“A8:I8”).ClearContents
With Range(“A9:I9”).ClearContents
Range(“c11:c28”).ClearContents
Range(“e11:e28”).ClearContents
Range(“f11:f28”).ClearContents
Range(“g11:g28”).ClearContents
Range(“h11:h28”).ClearContents
With Range(“i11:i28”).ClearContents
Range(“c31:c41”).ClearContents
Range(“d31:d41”).ClearContents
Range(“e31:e41”).ClearContents
Range(“g31:g41”).ClearContents
Range(“h31:h41”).ClearContents
Range(“i31:i41”).ClearContents
Range(“A44:a46”).ClearContents
Range(“c44:c46”).ClearContents
Range(“d44:d46”).ClearContents
Range(“e44:e46”).ClearContents
Range(“f44:f46”).ClearContents
Range(“g44:g46”).ClearContents
Range(“i44:i46”).ClearContents
Range(“A49:a100”).ClearContents
Range(“c49:c100”).ClearContents
Range(“d49:d100”).ClearContents
Range(“e49:e100”).ClearContents
Range(“f49:f100”).ClearContents
Range(“g49:g100”).ClearContents
Range(“h49:h100”).ClearContents
Range(“i49:I100”).ClearContents
End With
End With
End With
End Sub

Reply | Quote

dude i did it

Dim mydate As Date, newDate As Date, oldSheet As String, newSheet As String
If MsgBox(“Do you want to copy to the new day?”, vbYesNo) = vbNo Then Exit Sub
oldSheet = ActiveSheet.Name
If oldSheet Date Then
‘mydate = DateValue(“1” & oldSheet)
newDate = DateSerial(Month(mydate), day(mydate) + 1, 1)
newSheet = Format(Date, “mmmddyyyy”)
ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newSheet

YAHOO!!!!

Reply | Quote

never mind that last entry i’m getting errors now

runtim 1004

Reply | Quote

Please tell us which line causes that error.

Reply | Quote

hans I’m about to freakin scream here. everthing I try gives me a different error. now I’m getting run time error 13

what does that mean?

Reply | Quote

Error 1004 means that you’re referring to an object that has not been defined in the code.
Error 13 means that you’re using an incorrect data type, for example you’re trying to assign a text value to a numeric variable.
But you’ll have to tell us where exactly the errors occur.

Reply | Quote

mydate = DateValue(“1-” & oldSheet) is giving me a runtime error 13

Dim mydate As Date, newDate As Date, fulldate As Date, oldSheet As String, newSheet As String
If MsgBox(“Do you want to copy to the new day?”, vbYesNo) = vbNo Then Exit Sub
oldSheet = ActiveSheet.Name
If oldSheet Date Then
mydate = DateValue(“1-” & oldSheet)
newDate = fulldate + 1
newSheet = Format(Date, “mmmddyyyy”)
ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newSheet

Reply | Quote

If you’re naming a sheet with Format(Date, “mmmddyyyy”) it’ll have a name such as Feb232008. So “1” & oldSheet will look like 1-Feb232008 which is not a valid date string, of course.

Use this instead:

oldSheet = ActiveSheet.Name
newSheet = Format(Date, “mmmddyyyy”)
If oldSheet newSheet Then
ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newSheet
…
End If

Note that myDate and newDate aren’t used at all.

Reply | Quote

nothing happens!

Reply | Quote

apparently it works for oneday I can only get to feb232008 and then it starts to repeat itself

Reply | Quote

The code I posted looks at the current date. It’ll create a sheet for today (February 23), it won’t create a sheet for tomorrow (February 24) until it *is* tomorrow.

Reply | Quote

NOW THAT’S FUNNY !!!! I was just thinking the same thing.

Thank you very much for all of your assistance today. I am putting this to bed for now. I got a major headache… LOL

Thanks again buddy.

Reply | Quote

You don’t need the With and End With in the code. The lines

With Range(“A8:I8”).ClearContents
With Range(“A9:I9”).ClearContents
Range(“c11:c28”).ClearContents
Range(“e11:e28”).ClearContents
Range(“f11:f28”).ClearContents
Range(“g11:g28”).ClearContents
Range(“h11:h28”).ClearContents
With Range(“i11:i28”).ClearContents
Range(“c31:c41”).ClearContents
Range(“d31:d41”).ClearContents
Range(“e31:e41”).ClearContents
Range(“g31:g41”).ClearContents
Range(“h31:h41”).ClearContents
Range(“i31:i41”).ClearContents
Range(“A44:a46”).ClearContents
Range(“c44:c46”).ClearContents
Range(“d44:d46”).ClearContents
Range(“e44:e46”).ClearContents
Range(“f44:f46”).ClearContents
Range(“g44:g46”).ClearContents
Range(“i44:i46”).ClearContents
Range(“A49:a100”).ClearContents
Range(“c49:c100”).ClearContents
Range(“d49:d100”).ClearContents
Range(“e49:e100”).ClearContents
Range(“f49:f100”).ClearContents
Range(“g49:g100”).ClearContents
Range(“h49:h100”).ClearContents
Range(“i49:I100”).ClearContents
End With
End With
End With

can be simplified to

Range(“A7:I9”).ClearContents
Range(“C11:C28”).ClearContents
Range(“E11:I28”).ClearContents
Range(“C31:E41”).ClearContents
Range(“G31:I41”).ClearContents
Range(“A44:A46”).ClearContents
Range(“C44:G46”).ClearContents
Range(“I44:I46”).ClearContents
Range(“A49:A100”).ClearContents
Range(“G49:I100”).ClearContents

Reply | Quote