Jump to Worksheet TAB? (Keyboard Shortcut) (2000)

Topic

New Reply

I sometimes change a series of worksheet titles. Is there a keyboard shortcut to jump to the next TAB (not the next worksheet)? Or from a current worksheet to its own tab?

Reply | Quote

Replies

You could set something up in VBA to let you rename worksheets – which would have the same effect – but there is no way AFAIK of doing precisely what you are asking for.

Reply | Quote

I haven’t worked with VBA much. How is this like a macro (I used to use them in WordPerfect)? Is this a good example to start with?

Reply | Quote

Tal,
You can judge for your self between VBA and Macros. VBA is rooted in BASIC, so you’re not exactly replicating {keys}. Figure a simple task, like move a value from one cell to another, then activate the Macro Recorder (Tools>Macro>Record New Macro) and do the task. (You’ll need to make a name, and I suggest use a file you won’t need to save. Place the macro in that file – You’ll be asked for the location.) Next, use the keys and/or mouse to accomplish the task. Finally, keyboard shortcut Alt-F8 to see a list of (your 1) macro. Select it and click Edit. You will be looking at VBA code.

Compared to old style Macros, there’s lots more you can do to automate spreadsheets. It may take a bit to move up the learning curve (keep checking with the Lounge!), but soon you’ll shake the Macro dust off your shoes.

Errol

Reply | Quote

Tal,
You can judge for your self between VBA and Macros. VBA is rooted in BASIC, so you’re not exactly replicating {keys}. Figure a simple task, like move a value from one cell to another, then activate the Macro Recorder (Tools>Macro>Record New Macro) and do the task. (You’ll need to make a name, and I suggest use a file you won’t need to save. Place the macro in that file – You’ll be asked for the location.) Next, use the keys and/or mouse to accomplish the task. Finally, keyboard shortcut Alt-F8 to see a list of (your 1) macro. Select it and click Edit. You will be looking at VBA code.

Compared to old style Macros, there’s lots more you can do to automate spreadsheets. It may take a bit to move up the learning curve (keep checking with the Lounge!), but soon you’ll shake the Macro dust off your shoes.

Errol

Reply | Quote

I haven’t worked with VBA much. How is this like a macro (I used to use them in WordPerfect)? Is this a good example to start with?

Reply | Quote

You could set something up in VBA to let you rename worksheets – which would have the same effect – but there is no way AFAIK of doing precisely what you are asking for.

Reply | Quote

Ctrl+Page Up/Page Down takes you to the next sheet.
Right click the navugation keys to bring up a list of sheets
You can also set up one sheet to operate as a TOC with hyperlinks

Reply | Quote

Do you know how to activate the tab for that page (from the keyboard)?

Reply | Quote

Do you know how to activate the tab for that page (from the keyboard)?

Reply | Quote

There is no direct keyboard shortcut. Either double click on the name tab, or select Alt | o | h | r.

Reply | Quote

Well, why didn’t you SAY so! ?

Maybe you don’t think that’s short enough to be called a shortcut, but anything on the keyboard is a shortcut to me. Thanks.

Reply | Quote

Well, that’s a menu sequence to my way of thinking. For a list of keyboard shortcuts for Excel, see Colin’s Star Post 68646.

Reply | Quote

Well, that’s a menu sequence to my way of thinking. For a list of keyboard shortcuts for Excel, see Colin’s Star Post 68646.

Reply | Quote

Well, why didn’t you SAY so! ?

Maybe you don’t think that’s short enough to be called a shortcut, but anything on the keyboard is a shortcut to me. Thanks.

Reply | Quote

There is no direct keyboard shortcut. Either double click on the name tab, or select Alt | o | h | r.

Reply | Quote

Ctrl+Page Up/Page Down takes you to the next sheet.
Right click the navugation keys to bring up a list of sheets
You can also set up one sheet to operate as a TOC with hyperlinks

Reply | Quote

Tal:

Attached to this response is a workbook containing 2 Macros for your use.

The first Macro OldTabs will create a new Worksheet called “Tab Control”.
In Column B it will list the current names of each TAB in a workbook
In Column C the Macro will put the same names as in Column B
The user will then use Column C to type in the new desired TAB names. It is very important that Column C has a TAB name
for each worksheet in the workbook or the Macro will not function properly. If you don’t want to change a TAB leave the entry that is already in Column C.
Then run the second Macro NewTabs. This Macro will replace the TAB names with your entries in Column C and Delete Tab Control.

Since you are new to Macro’s I suggest the following to use the Macros.
1. Open the attached workbook and save it on your local drive.
2. When needed, open the attached workbook either open the workbook with the tab names you want to change or make it active.
3. Make sure the workbook with the tab names is the Active workbook
4. Then use the shortcut keys ALT + F8 or on the excel menu bar select Tools, Macro, Macro
5. Highligh the Macro you want and hit the Run button.

In the long run, you should move the Macros to your personal.xls worksheet and then put custom buttons on your toolbar, attach the Macros to the buttons.

Lastly, below I have included the Macro Code so it can be viewed by the people who view this post.

Regards,

TD

Sub OldTabs()
‘
‘ MyTabs Macro
‘ Macro recorded 1/23/2004
‘
‘Macro Will Create a new worksheet and list all Tab names
‘ Dim MyTabs As Worksheet
Dim MyValue As Integer
Dim WC As Integer
Dim Placeholder As String
Dim r As Integer
‘Procedure MyValue = 10
WC = Worksheets.Count
Worksheets.Item(WC).Activate
Worksheets.Add After:=Worksheets.Item(WC)
ActiveSheet.Name = “Tab Control”
Cells(7, 3).Value = “New Names”
For r = 1 To WC
Worksheets.Item®.Activate
Placeholder = ActiveSheet.Name
Worksheets(“Tab Control”).Activate
Cells(MyValue + r, 2).Value = Placeholder
Cells(MyValue + r, 3).Value = Placeholder
Next r
End Sub

Sub NewTabs()
‘
‘NewTabs Macro
‘Macro recorded 1/26/2004 by duthiet
‘
‘Maco will use list created in Tab Control [Column C]
‘that is then modified by User to rename the Tabs. MyValue = 10
WC = Worksheets.Count
For r = 1 To WC – 1
Worksheets(“Tab Control”).Activate
NewName = Cells(MyValue + r, 3).Value
Worksheets.Item®.Name = NewName
Next r
Application.DisplayAlerts = False
Worksheets(“Tab Control”).Delete
Application.DisplayAlerts = True
End Sub

Reply | Quote