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

avc0002

New Member
Joined
May 5, 2015
Messages
9
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

severynm

Board Regular
Joined
Jan 8, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows
That is something that is pretty straightforward to do. Using the macro recorder will give you something like the following:

Code:
    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.
VBA Code:
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
 
Solution

avc0002

New Member
Joined
May 5, 2015
Messages
9
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

Board Regular
Joined
Jan 8, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows
There probably are better solutions, but here's how I'd do it.

What we have so far:
VBA Code:
Dim sheetNameToCopy As String
sheetNameToCopy = ActiveSheet.Name

I would increment the date by adding two more lines:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,247
Messages
5,623,597
Members
415,981
Latest member
Baltwin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top