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

Put these two scripts in your Userform

Then when you open your userform all your sheet names will be loaded into the listbox

Then when you select a sheet name in the listbox you will taken to that sheet.

Open your userform with a script like this:
UserForm1.Show Modeless

Being modeless means you can work on the Worksheet with the userform displayed.

Make your userform very small so it does not block your sheet mine is vey narrow.

This script assumes your listbox is named ListBox1

Private Sub ListBox1_Click()
'Modified  5/13/2019  1:25:05 PM  EDT
Sheets(ListBox1.Value).Activate
End Sub
Private Sub UserForm_Initialize()
'Modified  5/13/2019  1:25:05 PM  EDT
Dim i As Long
ListBox1.Clear
For i = 1 To Sheets.Count
    ListBox1.AddItem Sheets(i).Name
Next
End Sub

 

[TimvMechelen]

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

Thanks a lot! It works. This is a great start for my navigation

 

[My Aswer Is This]

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

Glad you tried it. I think this is easier then putting three buttons on each sheet to go forward or backwards

And doing it this way as you add or delete sheets this Listbox will always have all the sheet names automatically loaded into the listbox. Now if we want to get fancy we could only load certain sheets and load then in a certain order if we wanted.

It would be something like:

Listbox1.additem "Alpha"
Listbox1.additem "Charlie
Listbox1.additem "November"

 

[TimvMechelen]

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.

I'm sorry I did not react to this post.
The code works, it creates a new sheet with the name format: YYYY.WW

So the thing you think is hard is to add sheets with the name for like 3 weeks ahead for example?
What if I put in the name manually in a userform? Is that possible?

Thanks a lot for your help and time!

 

[My Aswer Is This]

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

Making the format like Week 2019 24
Current Year Current Week

Is easy.

The problem is how do we tell the script to look at the last sheet see what that sheet is named and then make the new sheet name based off the last sheet name

Especially knowing what year should we use if we always wanted current year that's easy but if in December you wanted to create 3 new sheets one would have to be 2019 49 for example but next one would have to be 2020 1 or something like that. And all Months do not have the same number of weeks
We can have a Inputbox popup and you could enter the way you want the sheet named.

A Userform would also work you would need to put the sheet name you want in a Textbox for example

Which way would you like.

 

[My Aswer Is This]

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

And there are a lot of smarter people on this Forum who may have a answer.

 

[TimvMechelen]

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

Making the format like Week 2019 24
Current Year Current Week
Is easy.
The problem is how do we tell the script to look at the last sheet see what that sheet is named and then make the new sheet name based off the last sheet name

Especially knowing what year should we use if we always wanted current year that's easy but if in December you wanted to create 3 new sheets one would have to be 2019 49 for example but next one would have to be 2020 1 or something like that. And all Months do not have the same number of weeks
We can have a Inputbox popup and you could enter the way you want the sheet named.

A Userform would also work you would need to put the sheet name you want in a Textbox for example
Which way would you like.

A Userform or imputbox where I need to put the sheet name I want in a Textbox would be nice. Is it possible to show the last sheetname in that userform or imputbox so I know what the next sheetname needs to be?

 

[My Aswer Is This]

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

Try this:

Sub Add_New_Sheet()
'Modified 5/15/2019 1:24:54 PM  EDT
Dim ans As String
Dim Ln As String
Ln = Sheets(Sheets.Count).Name
ans = InputBox("Enter new sheet name", "Last sheet name shown below", Ln)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
End Sub

 

[My Aswer Is This]

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

Let's try this instead:
It warns you if you enter a duplicate sheet name or some not allowed name.

Sub Add_New_Sheet()
'Modified  5/15/2019  2:20:27 PM  EDT
On Error GoTo M
Dim ans As String
Dim Ln As String
Ln = Sheets(Sheets.Count).Name
ans = InputBox("Enter new sheet name", "Last sheet name shown below", Ln)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Exit Sub
M:
MsgBox "That sheet name is already used or is a improper name"
End Sub

 

[TimvMechelen]

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

Thanks this works!
But when I enter a name that already exists or chose cancel, I get an error