Today, in Episode #1389, Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen show us how to make Navigating a Workbook easier using both manual and coded means to create a TOC. Learn a few shortcuts, learn a little VBA and see how easy all of it is!
Transcript of the video:
Hey, all right, welcome back it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel we'll be joined by Mike Girvin from Excel Is Fun.
This is episode 76, Worksheet Navigation.
All right hey, this question sent in by DB Rutherford Ailin.
Let me make the worksheet little bit smaller look at this he has to have sales report for January, February, March, April, May and he wants a quick way to navigate from the menu here to the other tabs I'm going to throw this one over Mike first and have Mike solve it, come back and see if I can find a different way.
Mike show us what you've got.
Mike: Thanks MrExcel.
Wow, okay so, we need an essence a table of contents, but we want to click on any one of these and jump over the sheet.
So, if I click on this March right here I need to jump over to this sheet.
All right well, hyperlinks can do that now, usually we think of hyperlinks like going to a website, but no way.
Now, Insert, Hyperlink, I'm not sure I even know where it is there's a hyperlink, but the keyboard is control+K.
Now, usually we have an existing file.
Oh so, you could actually do a hyperlink to another file and open up another like sales report or a web page you just simply type right here place in this document is what we want to look at it.
It is looking and can see all of the sheets down here.
So, A1 that's the default you can change that.
I'm going to click on January and then click OK.
Now, if I click on this boom it jumps over to A1, January. I'm gonna come back to the menu.
Now, if i have a list like this I know how to do it manually I don't know any fancy other way to do this, but I can speed this up with keyboard shortcuts control+K.
Now, notice my cursor is right there if I hit tab, I get to the menu and that I can down arrow once i get to February, notice this is kind of highlighted with the dark shadow that means I can hit enter and then I hit enter again they already control+K tab arrow, arrow, arrow down to March enter, enter.
Control+K tab arrow down to April enter, enter.
Control+K tab a bunch of arrows enter, enter.
Control+K tab and then I go down to June. control, I forgot 2 enters.
Control+K tab and then down to July, let's see if I can do this, enter, enter.
Control+K tab down to the August enter, enter.
Control+K tab enter, enter.
Control+K tab almost done here enter, enter.
Control+K tab down to November.
Control+K tab all the way down and then enter.
I did that wrong their.
Control+K, I have to edit it, I clicked on the wrong one so, I need to go not December, but November.
So, you can edit them if you make a mistake, and then finally, control+K tab and then all the way down to the bottom.
All right now, let's see if this works so I'm going to click on April and sure enough I'm on April, click on September.
Now, what I would like to do is have a link to go back to the menu.
So, I'm going to go over to January and I'd love to highlight all the sheets and do it at once but it doesn't let me do that.
So I'm going to click here and control+K select A1 menu, click OK.
Now, I'm gonna copy that, did I do that right?
Control+K, yeah that's right yeah I didn't do any friendly text I'm going to change the text up here i'm going to type something like oh Back To Menu.
Now, I'm going to copy this cell dance and dance marks around.
I'm going to click on February, I'm going to scroll over and before I click on December I'm holding shift to highlight all the cells then I'm going to click in A11 and control V.
So, I've paste a copy and pasted that hyperlink.
So, on any sheet I should be able to just back to this and I'm going to click on this November it goes to November and back to menu.
All right, throwback to MrExcel.
Hey, Mike that was excellent i love the keyboard shortcuts to speed that up also when group mode wouldn't let you do insert hyperlink very clever putting the hyperlink back to menu on January and then copying and pasting while you're in group mode very very clever.
Well, of course I'm gonna do VBA here and you'll notice back in my worksheet.
I got rid of all of the sheet names, I just want to be able to build those on the fly and I'm gonna use my, one of my favorite loops here it's called the For Each loop, For Each WS.
WS is a variable name in ActiveWorkbook.Worksheets that's going to go through every single worksheet that it happens to find today.
This is very general purpose whatever, whenever you haven't run into checks to make sure that this WS.Name is not equal to men you i don't need a link to the menu on the menu.
If it's not, I go activate the menu and then select row 3 plus a counter you'll see the counter starts out at 0.
So, the first worksheet it finds is going to be in Row 3 and then Row 4 and then Row 5 we're bumping the counter up down here select that cell usually in my Macros, I don't like to select cells, but the Macro Recorder you know just gaming code which it always does then had a selection in here we are.
It's five o'clock too lazy to come the other way, but this is gonna work.
So, in our our menu hyperlinks not add the anchor is the selected cell it's funny that address is for going out to another web page we're not doing that we're just going to the name of the worksheet January ! A1 and then putting January as the text to display that's what the dialog box calls that friendly text and you know then very nice where you put the back to menu there I also added that to the Macro.
So, we go out and select January and then I selected a row 1, G, G1 is where I always put my back is up in the upper right hand corner there and WS.hyperlinks.add, you can see I just copy this down and forgot to change that around how the anchor is going to be the selected cell and again this time we're always going back to B3 text to display is back to menu.
Finally, increment the counter and if cover next time worksheets, that were to go up and do the next one and finally end.
So, let's just give it a quick little run here and there it is.
I'm going to take a look at our code so there's back to menu on December when we click that we go back and you see that all of our hyperlinks are there now what's really nice about this I'm gonna go through and clear these, Alt E+A+A, and I'm going to add some new worksheets in just make a few copies and then run that code again I'll do Alt+F8 and add hyperlinks Run.
You see that even though I've added some new worksheets in.
I'm sorry back to many that's great way to go those automatically show up in the list.
So, very general way of adding the hyperlinks with VBA they're.
All right well, hey, I wanna thank everyone for stopping by.
We'll see you next time dueling Excel podcast form MrExcel and Excel is Fun.
I'm Bill Jelen from MrExcel we'll be joined by Mike Girvin from Excel Is Fun.
This is episode 76, Worksheet Navigation.
All right hey, this question sent in by DB Rutherford Ailin.
Let me make the worksheet little bit smaller look at this he has to have sales report for January, February, March, April, May and he wants a quick way to navigate from the menu here to the other tabs I'm going to throw this one over Mike first and have Mike solve it, come back and see if I can find a different way.
Mike show us what you've got.
Mike: Thanks MrExcel.
Wow, okay so, we need an essence a table of contents, but we want to click on any one of these and jump over the sheet.
So, if I click on this March right here I need to jump over to this sheet.
All right well, hyperlinks can do that now, usually we think of hyperlinks like going to a website, but no way.
Now, Insert, Hyperlink, I'm not sure I even know where it is there's a hyperlink, but the keyboard is control+K.
Now, usually we have an existing file.
Oh so, you could actually do a hyperlink to another file and open up another like sales report or a web page you just simply type right here place in this document is what we want to look at it.
It is looking and can see all of the sheets down here.
So, A1 that's the default you can change that.
I'm going to click on January and then click OK.
Now, if I click on this boom it jumps over to A1, January. I'm gonna come back to the menu.
Now, if i have a list like this I know how to do it manually I don't know any fancy other way to do this, but I can speed this up with keyboard shortcuts control+K.
Now, notice my cursor is right there if I hit tab, I get to the menu and that I can down arrow once i get to February, notice this is kind of highlighted with the dark shadow that means I can hit enter and then I hit enter again they already control+K tab arrow, arrow, arrow down to March enter, enter.
Control+K tab arrow down to April enter, enter.
Control+K tab a bunch of arrows enter, enter.
Control+K tab and then I go down to June. control, I forgot 2 enters.
Control+K tab and then down to July, let's see if I can do this, enter, enter.
Control+K tab down to the August enter, enter.
Control+K tab enter, enter.
Control+K tab almost done here enter, enter.
Control+K tab down to November.
Control+K tab all the way down and then enter.
I did that wrong their.
Control+K, I have to edit it, I clicked on the wrong one so, I need to go not December, but November.
So, you can edit them if you make a mistake, and then finally, control+K tab and then all the way down to the bottom.
All right now, let's see if this works so I'm going to click on April and sure enough I'm on April, click on September.
Now, what I would like to do is have a link to go back to the menu.
So, I'm going to go over to January and I'd love to highlight all the sheets and do it at once but it doesn't let me do that.
So I'm going to click here and control+K select A1 menu, click OK.
Now, I'm gonna copy that, did I do that right?
Control+K, yeah that's right yeah I didn't do any friendly text I'm going to change the text up here i'm going to type something like oh Back To Menu.
Now, I'm going to copy this cell dance and dance marks around.
I'm going to click on February, I'm going to scroll over and before I click on December I'm holding shift to highlight all the cells then I'm going to click in A11 and control V.
So, I've paste a copy and pasted that hyperlink.
So, on any sheet I should be able to just back to this and I'm going to click on this November it goes to November and back to menu.
All right, throwback to MrExcel.
Hey, Mike that was excellent i love the keyboard shortcuts to speed that up also when group mode wouldn't let you do insert hyperlink very clever putting the hyperlink back to menu on January and then copying and pasting while you're in group mode very very clever.
Well, of course I'm gonna do VBA here and you'll notice back in my worksheet.
I got rid of all of the sheet names, I just want to be able to build those on the fly and I'm gonna use my, one of my favorite loops here it's called the For Each loop, For Each WS.
WS is a variable name in ActiveWorkbook.Worksheets that's going to go through every single worksheet that it happens to find today.
This is very general purpose whatever, whenever you haven't run into checks to make sure that this WS.Name is not equal to men you i don't need a link to the menu on the menu.
If it's not, I go activate the menu and then select row 3 plus a counter you'll see the counter starts out at 0.
So, the first worksheet it finds is going to be in Row 3 and then Row 4 and then Row 5 we're bumping the counter up down here select that cell usually in my Macros, I don't like to select cells, but the Macro Recorder you know just gaming code which it always does then had a selection in here we are.
It's five o'clock too lazy to come the other way, but this is gonna work.
So, in our our menu hyperlinks not add the anchor is the selected cell it's funny that address is for going out to another web page we're not doing that we're just going to the name of the worksheet January ! A1 and then putting January as the text to display that's what the dialog box calls that friendly text and you know then very nice where you put the back to menu there I also added that to the Macro.
So, we go out and select January and then I selected a row 1, G, G1 is where I always put my back is up in the upper right hand corner there and WS.hyperlinks.add, you can see I just copy this down and forgot to change that around how the anchor is going to be the selected cell and again this time we're always going back to B3 text to display is back to menu.
Finally, increment the counter and if cover next time worksheets, that were to go up and do the next one and finally end.
So, let's just give it a quick little run here and there it is.
I'm going to take a look at our code so there's back to menu on December when we click that we go back and you see that all of our hyperlinks are there now what's really nice about this I'm gonna go through and clear these, Alt E+A+A, and I'm going to add some new worksheets in just make a few copies and then run that code again I'll do Alt+F8 and add hyperlinks Run.
You see that even though I've added some new worksheets in.
I'm sorry back to many that's great way to go those automatically show up in the list.
So, very general way of adding the hyperlinks with VBA they're.
All right well, hey, I wanna thank everyone for stopping by.
We'll see you next time dueling Excel podcast form MrExcel and Excel is Fun.
