CODEWITHSUNDEEP
vbaexcelworksheet
MrPhooky
MrPhooky

Reputation: 139

Excel macros, adding new sheets each day

So I am attempting to create a sort of daily food diary in Excel and I know what I want it to do, I am just struggling implementing said ideas...
I have created a template that I want the user to enter into each day, but I don't want the user to have to create a new worksheet each day to achieve this, I want to create a macro that will run once Excel has been opened and will check the date that the last entry sheet was filled in, and if it is less that the current date, create a new worksheet and display that one to be filled in!

Here is what I have found and tried so far but it isn't doing anything and coming up with errors.

Private Sub Workbook_Open()
Dim sh As Worksheet
Dim shName As String

'name of the sheet template
shName = "Food Diary.xltm"

'Insert sheet template
With ThisWorkbook
    If .Range("A1") < Date Then
        .Range("A1") = Date
        Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
                            after:=.Sheets(.Sheets.Count))
        'Give the sheet a name, today's date in this example
        On Error Resume Next
        sh.Name = "Day" & " " & Worksheets.Count
        If Err.Number > 0 Then
            MsgBox "Change the name of Sheet : " & sh.Name & " manually"
            Err.Clear
        End If
        On Error GoTo 0
    End If
    End With

End Sub

Hopefully you can see what I am trying to achieve with this.
If today's date is greater than the latest date in the sheet, create a new one named Day [number of sheets], I'm not sure about the date thing, as if date is entered into "A1" and a day passes would that not update when the workbook is opened?

I also found something that seemed to be about hiding a sheet once the new one was created, This is something that would be quite good, it would be nice to have only the current day's sheet showing, but the others should still be accessible via like buttons that would scroll through each one after each press, I think I will be able to do the button coding but I am not sure about the visibility of the sheets and whether I would be able to unhide them once hidden, any help with this also would be great.

Upvotes: 0

Views: 3689

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Problem is you're assigning to A1 before you add the new sheet. So, you're writing to the ActiveSheet, not the sheet that you want to create (which has not yet been created!).

Something like this, if I understand correctly. Should query the Active sheet Range A1, compare to today's date, and add new sheet, hide the old sheet, etc.

Private Sub Workbook_Open()
Dim thisSheet as Sheet
Dim sh As Worksheet
Dim shName As String

'name of the sheet template
shName = "Food Diary.xltm"

'#### I like to use variables for worksheets:
Set thisSheet = ThisWorkbook.ActiveSheet

'Insert sheet template
With thisSheet
    If .Range("A1") < Date Then
        Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
                            after:=.Sheets(.Sheets.Count))
        '#### Put today's date in the new sheet:
        sh.Range("A1") = Date

       'Give the sheet a name, today's date in this example
        On Error Resume Next
        sh.Name = "Day " & Worksheets.Count
        If Err.Number > 0 Then
            MsgBox "Change the name of Sheet : " & sh.Name & " manually"
            Err.Clear
        End If
        On Error GoTo 0

       '#### Hide the old sheet
       .Visible = xlSheetHidden

    End If
End With

End Sub

Upvotes: 1

Related Questions