Discussion Locked

LINKING WORKSHEETS

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a workbook in excel with 9 worksheets. I need to able to tab from one worksheet to another and be on the same row.

3 replies

by peyison

Here's a VBA macro that will allow you to do this. To use it:

* From Excel, Select Tools -> Macro -> Visual Basic Editor

* Press [Ctrl]+R to open the Project Window (or select View -> Project Explorer)

* In the Project Window (left window) - double click on the "ThisWorkbook" item under your workbook name.

* Paste the code below into the code window (right window) (paste below the words "Option Explicit" if they are there)

* Close the VBA window

* Choose Tools -> Macro -> Macros

* Select the macro TabSheets and press "Options"

* In the Shortcut Key box, enter a letter - like "t"

* Press OK and Cancel

That should do it. You should be able to press [Ctrl]+t to tab between your sheets.
(If you entered an upper case letter, you'll have to press [Ctrl]+[Shift]+T)

Here's the code:
---------------------
Public Sub TabSheets()
Dim iCurSheet As Integer
Dim lCurRow As Long
Dim lCurCol As Long
Dim iNewSheet As Integer

'--get the index of the current sheet
iCurSheet = ActiveSheet.Index

'--get the current row and column number
lCurRow = ActiveCell.Row
lCurCol = ActiveCell.Column

'--check to see if this is the last sheet in the workbook
'--(if so, need to tab to the first)
If iCurSheet = ActiveWorkbook.Sheets.Count Then
iNewSheet = 1
Else
iNewSheet = iCurSheet + 1
End If

'--activate the next sheet
ActiveWorkbook.Sheets(iNewSheet).Activate

'--activate the same cell that was current in the old sheet
'--can change lCurCol to 1 if you want to activate the first column in the row
ActiveSheet.Cells(lCurRow, lCurCol).Activate
End Sub
---------------------

Hope this helps.

This discussion is closed to new replies.