New Sheet with automatic naming & easy file navigation

[TimvMechelen]

Hi all,

I would like to have an excel file where I can easily navigate through the sheets.
I have sheets for each week, but when I need an extra week, I would like to add a next sheet with one click. A macro is required for that.
When that macro adds a new sheet, I would like the macro to rename that sheet a specific way: with year and weeknumber.

Other specific information is described in the example below.
https://www.dropbox.com/s/hseeolq720z88u3/Example Sheet navigation.xlsm?dl=0


Thanks for your help.

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

I never open other peoples Workbooks so all I know it what you tell me.

Doing what you want should be easy just be sure and provide specific details.

I find the easiest way to navigate a large number of sheets is to use a UserForm listbox with all the sheet names. Click on the listbox sheet name you want to go to and presto you are there.

If this sounds like something you may want want then let me know.
Or another way is to have a cell on each sheet where you can click on a Hyperlink to take you back to a master sheet that has hyperlinks in column A for every sheet.

The Userform approach is the best in my opinion and is easily updated as you add and delete sheets.

The small Userform can be visible as you work on your sheet and is updated every time you open the userform with any new sheet names

I can provide more details if you need it.

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

Here is a Script to add a new sheet:

Sub Add_New_Sheet()
'Modified 5/10/2019 8:04:48 PM  EDT
On Error GoTo M
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(Date, "YYYY WW")
Exit Sub
M:
MsgBox "That sheet name already exist"
End Sub

 

[TimvMechelen]

Re: New Sheet with automatic naming & easy file navigation | Example added

To give you a more specific description:
I have 3 sheets now: "Home", "Overview" and "WEEK.2019.19".
1) On the "Home"sheet, I would like to have a knob to add a new sheet. So if the last sheet (with the WEEK.*YEAR*.*WEEK* format) is "WEEK.2019.19", the sheet to add would be "WEEK.2019.20". If the last sheet with that format is the last week of the year, the macro should create a new sheet with "WEEK.2020.01" for example. If I want to add 3 new sheets (3 times clicking on the knob) this should add 3 new sheets as described.

2) On each sheet with the WEEK.*YEAR*.*WEEK* format, I would like to have 4 macro knobs or hyperlinks that can navigate me to the next and previous sheet (week) and to the "Home" and "Overview" sheets.

3) On each sheet with the WEEK.*YEAR*.*WEEK* format, I would like to have the sheetname in cell C1. I used the =CELL(Filename) for that now, but that doesn't work since it's only showing the name of the active sheet.

The userform idea would be great to have on the "Home" sheet! Can you please tell me how to create that? And will it update automaticly when I added a new sheet?
Thanks!

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

This is a hard one. I will pass on this at I'm sure some others users on this Forum will be able to help you.

 

[TimvMechelen]

Re: New Sheet with automatic naming & easy file navigation | Example added

Maybe you can only help me with the userform?

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

As far as the Userform. I'm not able or maybe I should say not willing to step you through ever little step of building a UserForm.

Sort of like trying to tell a person how to drive a car if they know nothing about a car or even what a Car is.

So tell me do you even know what a UserForm is?
Do you know how to Open and Close a UserForm?

Do you know what a Listbox is ?

If you do not know the answers to any of these questions it would be very hard to help you.

If you do know the answers to these questions then build yourself a Userform and create the buttons you need to Open the Userform and close the Userform and put a listbox on the UserForm and get back with me.

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

I did provide you with a script to create a new sheet and you mentioned nothing about if that script worked.

The format may not be exact. It creates a new sheet and gives it the name of the current week and Year.

Not sure why you said nothing about this script.

You should be able to see the formatting and modify it to your needs.

Or is this nothing like you want.

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

Creating the new sheets the way you want would be a very difficult task for me.
Especially when you say every time you click the button you want a new sheet made.

I thought earlier you would be creating a new sheet for just the current week.
So for example if today is 5/12/2019 the sheet would have todays year 2019 and todays week lets say week 25

But when you want to click the button 5 times on the same day and create a new sheet and try and figure out what year and month number the new sheet should be would be very difficult for me.

That's why someone else on this forum will be needed to help you.

 

[TimvMechelen]

Re: New Sheet with automatic naming & easy file navigation | Example added

I know the very basic things of VBA and userforms. I have the listbox on the userform now.