Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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". <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    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"?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  5. #5
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  6. #6
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    The line

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

    copies all sheets in the workbook. Try this instead:

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

  8. #8
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    thank you again hans,

  9. #9
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  11. #11
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  13. #13
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  14. #14
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  15. #15
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    never mind that last entry i'm getting errors now

    runtim 1004

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •