Creating New Sheet With Button Jumping Date & Naming Sheet the New Date

[avc0002]

hello everyone,

I work for a construction company and work with a lot of older people that aren't the best with computers.

I've trained our superintendents to fill out the timesheets on a computer but just having them create a new tab and changing the date has proved to be an issue.

Our timesheet file currently has one timesheet per tab/sheet meaning each day is one tab. (example, there is a timesheet for 2021-01-11, one for 2021-01-12, one for 2021-01-13 and so on)


I usually go into the file and make a few tabs and change the date for them but if there was just a button I feel like they'd think I'm a wizard or something. hahaha.

I'd like to create a button that duplicates the current sheet. and if possible jumps the date one day for the title of the tab/sheet.




Thank you very much.


if you'd like any additional info from me please let me know.

 

[severynm]

That is something that is pretty straightforward to do. Using the macro recorder will give you something like the following:

    Sheets("2021-01-13").Copy After:=Sheets(1)
    Sheets("2021-01-13 (2)").Name = "newsheetname"

If you always want it to duplicate the currently selected sheet, use ActiveSheet.Name instead to be a little more robust. You can then increment newsheetname as necessary. Worksheets.Copy also lets you specify if you want to insert the sheet before or after the indicated worksheet, depending on what you want to do. Add it to a button, and you've got a nice little macro.

Private Sub CommandButton1_Click()
    Dim sheetNameToCopy As String
    sheetNameToCopy = ActiveSheet.Name
    ThisWorkbook.Worksheets(sheetNameToCopy).Copy Before:=ThisWorkbook.Worksheets(sheetNameToCopy)
    ThisWorkbook.Worksheets(sheetNameToCopy & " (2)").Name = "newsheetname"
End Sub

 

[avc0002]

Wow that was super fast.

I've been playing around with it and I think I've goofed up along the way.

forgive my likely simple / dumb questions.


I've ran into a snag.
I added the button.
I used the macro recorder.
added the exact code you have except changed the worksheet.copy part to be after.

It's creating a new sheet. which is super cool.

but I cant sort out how to jump the date. so I figured I'd ask before I spend all night to just confuse myself more.

Thanks.

 

[severynm]

There probably are better solutions, but here's how I'd do it.

What we have so far:

Dim sheetNameToCopy As String
sheetNameToCopy = ActiveSheet.Name

I would increment the date by adding two more lines:

Dim newSheetName As String
newSheetName = Format(CDate(sheetNameToCopy) + 1, "yyyy-mm-dd")

This: 1) Grabs the old sheet name and converts it to a number of type Date (CDate is basically convert to date), 2) adds 1 to increment the days, 3) Use Format() to convert our numerical date back to a string in the format that you're looking for.

If you want to have it automatically increment days accross a new month (go from 01-31 -> 02-01). A little more logic is required. This might help.

 

[avc0002]

Thank you so much.

Really appreciate your time.



Cheers.