How do I make form fill out to different sheets based on the month?

I have an exercise and diet log that I fill out every other day and I want the data to move to another sheet. Sheet 1 looks like a worksheet and I want it to move (I think the best way to do this is with a button) to sheet 2.

But on the first day of a new month I want it to start filling out in a new sheet. So I know how to create the new sheet using

Sub Macro1()

    Dim szTodayDate As String
    szTodayDate = Format(Date, "mmmm")
    On Error GoTo MakeSheet
    Sheets(szTodayDate).Activate
    Exit Sub

MakeSheet:

    Dim Importsheet As Worksheet 'Imports worksheet from a closed workbook
    If Day(Date) = 1 Then Sheets.Add Type:= _
    "C:\Users\Valerie\Documents\Template.xls"

    If Day(Date) = 1 Then ActiveSheet.Name = szTodayDate

End Sub

So on the first day of the month when the macro is run it creates a new sheet, but I'm not sure how to send the correct day to the correct line on the correct sheet, and then when a new sheet is created on the first day of the month, I'm not sure how to make the form start going to the new sheet.

Sheet 1

DAILY WORKOUT AND DIET LOG          
Valerie         Wednesday
Workout         A

Total Miles Run         Plank Time (minutes)
6           5

Situps          Squats
30          30

Pushups         
20

Sheet 2

Date   Workout   Miles  Plank   Situps  Squats  Pushups

Valerie

Posted 2016-05-03T18:53:20.063

Reputation: 3

1Seeing as we don't know anything about the structure of any of your sheets there is no way we can help you "send the correct day to the correct line on the correct sheet" – gtwebb – 2016-05-03T19:00:32.467

Some diagrams of your data would help us understand your question. Please [edit] your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question.

– DavidPostill – 2016-05-03T19:59:56.523

1I added some sections of the sheets to kind of show what I'm talking about. Hope this works. @DavidPostill Thanks for the excel tool. – Valerie – 2016-05-04T13:33:21.583

So want I want is is a button that runs a macro that I hit at the end of the day when I've recorded all of my data in sheet one, that puts everything in sheet two. Then the next day I'll hit the button again with new data in sheet 1 and the data is sent to the next line down on sheet two from the previous days data. How do I do this? – Valerie – 2016-05-04T15:45:34.103

How do I make form fill out to different sheets based on the month?

No answers