I had a workbook with 100 worksheets. As this is a big workbook, in order for
me to get to the correct worksheet, I had created 4 menus which were placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend the
title of the 100 worksheets while the other 3 menus will created by using the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100 worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a problem
on the other 3 menus. The macro buttons under column B does not follow column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

So, you have a sheet that contains all of your worksheets and then a
button to open the sheet? If so, why not just create a table of
contents with hyperlinks to all of the sheets. That should allow you
to sort without any problems.

Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw = ActiveWorkbook.Worksheets _
.Add(Befo=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[A1] = "Table Of Contents"
.[A2] = ActiveWorkbook.Name & " Worksheets"
.[A1].Font.Size = 14
.[A2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
End Sub

vcff wrote:
I had a workbook with 100 worksheets. As this is a big workbook, in order for
me to get to the correct worksheet, I had created 4 menus which were placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend the
title of the 100 worksheets while the other 3 menus will created by using the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100 worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a problem
on the other 3 menus. The macro buttons under column B does not follow column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

sorry as I am still very green on this. Do I just need to copy the macro
under the view code?

"JW" wrote:

So, you have a sheet that contains all of your worksheets and then a
button to open the sheet? If so, why not just create a table of
contents with hyperlinks to all of the sheets. That should allow you
to sort without any problems.

Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw = ActiveWorkbook.Worksheets _
.Add(Befo=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[A1] = "Table Of Contents"
.[A2] = ActiveWorkbook.Name & " Worksheets"
.[A1].Font.Size = 14
.[A2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
End Sub

vcff wrote:
I had a workbook with 100 worksheets. As this is a big workbook, in order for
me to get to the correct worksheet, I had created 4 menus which were placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend the
title of the 100 worksheets while the other 3 menus will created by using the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100 worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a problem
on the other 3 menus. The macro buttons under column B does not follow column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

Instead of a macro button for each sheet why not just use a double_click
event such as the one I use for my menu sheet. Right click sheet tabview
codecopy/paste this. Then, simply double click on the sheet name typed in
cells in col A (or, as written, anywhere on the sheet). BTW, if you sort
sheets are set up the same you could sort any sheet from the menu sheet
without going to the individual sheets. Perhaps you don't need 100 sheets???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
I had a workbook with 100 worksheets. As this is a big workbook, in order
for
me to get to the correct worksheet, I had created 4 menus which were
placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend the
title of the 100 worksheets while the other 3 menus will created by using
the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100
worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a problem
on the other 3 menus. The macro buttons under column B does not follow
column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

Hi Guillett

tnks for the help.

My worksheets are named by numbering, 1 to 100 plus 4 menus

I copied the following by Right click the menu1 sheet tabview
codepaste , close and ret to excel

when I go to menu1 double click cellA1, no respone

"Don Guillett" wrote:

Instead of a macro button for each sheet why not just use a double_click
event such as the one I use for my menu sheet. Right click sheet tabview
codecopy/paste this. Then, simply double click on the sheet name typed in
cells in col A (or, as written, anywhere on the sheet). BTW, if you sort
sheets are set up the same you could sort any sheet from the menu sheet
without going to the individual sheets. Perhaps you don't need 100 sheets???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
I had a workbook with 100 worksheets. As this is a big workbook, in order
for
me to get to the correct worksheet, I had created 4 menus which were
placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend the
title of the 100 worksheets while the other 3 menus will created by using
the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100
worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a problem
on the other 3 menus. The macro buttons under column B does not follow
column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

Change to this instead. Did you put in the sheet module? Did you save the
workbook after installing? Do you have the sheet number ie: 1 or 12
or 100 typed in a cell. If so, the macro will work to goto any sheet with
the name of the sheet typed in the cell you double click on. Again, why 100
sheets?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
Hi Guillett

tnks for the help.

My worksheets are named by numbering, 1 to 100 plus 4 menus

I copied the following by Right click the menu1 sheet tabview
codepaste , close and ret to excel

when I go to menu1 double click cellA1, no respone

"Don Guillett" wrote:

Instead of a macro button for each sheet why not just use a double_click
event such as the one I use for my menu sheet. Right click sheet tabview
codecopy/paste this. Then, simply double click on the sheet name typed
in
cells in col A (or, as written, anywhere on the sheet). BTW, if you sort
sheets are set up the same you could sort any sheet from the menu sheet
without going to the individual sheets. Perhaps you don't need 100
sheets???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
I had a workbook with 100 worksheets. As this is a big workbook, in
order
for
me to get to the correct worksheet, I had created 4 menus which were
placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend
the
title of the 100 worksheets while the other 3 menus will created by
using
the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100
worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a
problem
on the other 3 menus. The macro buttons under column B does not follow
column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

HI JW

After several tries, able to figure out and find that this is indeed a much
easier way to do.

tnks a lot for the help.

"JW" wrote:

So, you have a sheet that contains all of your worksheets and then a
button to open the sheet? If so, why not just create a table of
contents with hyperlinks to all of the sheets. That should allow you
to sort without any problems.

Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw = ActiveWorkbook.Worksheets _
.Add(Befo=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[A1] = "Table Of Contents"
.[A2] = ActiveWorkbook.Name & " Worksheets"
.[A1].Font.Size = 14
.[A2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
End Sub

vcff wrote:
I had a workbook with 100 worksheets. As this is a big workbook, in order for
me to get to the correct worksheet, I had created 4 menus which were placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend the
title of the 100 worksheets while the other 3 menus will created by using the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100 worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a problem
on the other 3 menus. The macro buttons under column B does not follow column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

under view code, sheet 1 right click n insert module, copy/paste and close
exit to excel but did not working. 100 sheets contain a lot of forms and info

"Don Guillett" wrote:

Change to this instead. Did you put in the sheet module? Did you save the
workbook after installing? Do you have the sheet number ie: 1 or 12
or 100 typed in a cell. If so, the macro will work to goto any sheet with
the name of the sheet typed in the cell you double click on. Again, why 100
sheets?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
Hi Guillett

tnks for the help.

My worksheets are named by numbering, 1 to 100 plus 4 menus

I copied the following by Right click the menu1 sheet tabview
codepaste , close and ret to excel

when I go to menu1 double click cellA1, no respone

"Don Guillett" wrote:

Instead of a macro button for each sheet why not just use a double_click
event such as the one I use for my menu sheet. Right click sheet tabview
codecopy/paste this. Then, simply double click on the sheet name typed
in
cells in col A (or, as written, anywhere on the sheet). BTW, if you sort
sheets are set up the same you could sort any sheet from the menu sheet
without going to the individual sheets. Perhaps you don't need 100
sheets???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
I had a workbook with 100 worksheets. As this is a big workbook, in
order
for
me to get to the correct worksheet, I had created 4 menus which were
placed
in such an order that at all times, I will be able to see one menu when
working in this workbook. Menu1 is the worksheet which I list or amend
the
title of the 100 worksheets while the other 3 menus will created by
using
the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100
worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a
problem
on the other 3 menus. The macro buttons under column B does not follow
column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

Let's try again.
On the sheet with the sheet names typed into cells on col A. That is where
you right clickselect view codeinsert the code. It goes into the SHEET
module, NOT into a regular module...... You may send me the workbook, if
desired to the address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
under view code, sheet 1 right click n insert module, copy/paste and close
exit to excel but did not working. 100 sheets contain a lot of forms and
info

"Don Guillett" wrote:

Change to this instead. Did you put in the sheet module? Did you save the
workbook after installing? Do you have the sheet number ie: 1 or
12
or 100 typed in a cell. If so, the macro will work to goto any sheet
with
the name of the sheet typed in the cell you double click on. Again, why
100
sheets?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
Hi Guillett

tnks for the help.

My worksheets are named by numbering, 1 to 100 plus 4 menus

I copied the following by Right click the menu1 sheet tabview
codepaste , close and ret to excel

when I go to menu1 double click cellA1, no respone

"Don Guillett" wrote:

Instead of a macro button for each sheet why not just use a
double_click
event such as the one I use for my menu sheet. Right click sheet
tabview
codecopy/paste this. Then, simply double click on the sheet name
typed
in
cells in col A (or, as written, anywhere on the sheet). BTW, if you
sort
sheets are set up the same you could sort any sheet from the menu
sheet
without going to the individual sheets. Perhaps you don't need 100
sheets???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
I had a workbook with 100 worksheets. As this is a big workbook, in
order
for
me to get to the correct worksheet, I had created 4 menus which were
placed
in such an order that at all times, I will be able to see one menu
when
working in this workbook. Menu1 is the worksheet which I list or
amend
the
title of the 100 worksheets while the other 3 menus will created by
using
the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100
worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the
correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a
problem
on the other 3 menus. The macro buttons under column B does not
follow
column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

Hi Guillert

sorry for the late reply. Had just tried and got the "double-click" working.

Thanks for the help.

Have a nice day :-)

"Don Guillett" wrote:


Let's try again.
On the sheet with the sheet names typed into cells on col A. That is where
you right clickselect view codeinsert the code. It goes into the SHEET
module, NOT into a regular module...... You may send me the workbook, if
desired to the address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
under view code, sheet 1 right click n insert module, copy/paste and close
exit to excel but did not working. 100 sheets contain a lot of forms and
info

"Don Guillett" wrote:

Change to this instead. Did you put in the sheet module? Did you save the
workbook after installing? Do you have the sheet number ie: 1 or
12
or 100 typed in a cell. If so, the macro will work to goto any sheet
with
the name of the sheet typed in the cell you double click on. Again, why
100
sheets?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
Hi Guillett

tnks for the help.

My worksheets are named by numbering, 1 to 100 plus 4 menus

I copied the following by Right click the menu1 sheet tabview
codepaste , close and ret to excel

when I go to menu1 double click cellA1, no respone

"Don Guillett" wrote:

Instead of a macro button for each sheet why not just use a
double_click
event such as the one I use for my menu sheet. Right click sheet
tabview
codecopy/paste this. Then, simply double click on the sheet name
typed
in
cells in col A (or, as written, anywhere on the sheet). BTW, if you
sort
sheets are set up the same you could sort any sheet from the menu
sheet
without going to the individual sheets. Perhaps you don't need 100
sheets???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
I had a workbook with 100 worksheets. As this is a big workbook, in
order
for
me to get to the correct worksheet, I had created 4 menus which were
placed
in such an order that at all times, I will be able to see one menu
when
working in this workbook. Menu1 is the worksheet which I list or
amend
the
title of the 100 worksheets while the other 3 menus will created by
using
the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100
worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the
correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had a
problem
on the other 3 menus. The macro buttons under column B does not
follow
column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks

Glad to help. Call me Don

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
Hi Guillert

sorry for the late reply. Had just tried and got the "double-click"
working.

Thanks for the help.

Have a nice day :-)

"Don Guillett" wrote:


Let's try again.
On the sheet with the sheet names typed into cells on col A. That is
where
you right clickselect view codeinsert the code. It goes into the SHEET
module, NOT into a regular module...... You may send me the workbook, if
desired to the address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
under view code, sheet 1 right click n insert module, copy/paste and
close
exit to excel but did not working. 100 sheets contain a lot of forms
and
info

"Don Guillett" wrote:

Change to this instead. Did you put in the sheet module? Did you save
the
workbook after installing? Do you have the sheet number ie: 1
or
12
or 100 typed in a cell. If so, the macro will work to goto any sheet
with
the name of the sheet typed in the cell you double click on. Again,
why
100
sheets?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
Hi Guillett

tnks for the help.

My worksheets are named by numbering, 1 to 100 plus 4 menus

I copied the following by Right click the menu1 sheet tabview
codepaste , close and ret to excel

when I go to menu1 double click cellA1, no respone

"Don Guillett" wrote:

Instead of a macro button for each sheet why not just use a
double_click
event such as the one I use for my menu sheet. Right click sheet
tabview
codecopy/paste this. Then, simply double click on the sheet name
typed
in
cells in col A (or, as written, anywhere on the sheet). BTW, if you
sort
sheets are set up the same you could sort any sheet from the menu
sheet
without going to the individual sheets. Perhaps you don't need 100
sheets???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
Cancel
As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vcff" wrote in message
...
I had a workbook with 100 worksheets. As this is a big workbook,
in
order
for
me to get to the correct worksheet, I had created 4 menus which
were
placed
in such an order that at all times, I will be able to see one
menu
when
working in this workbook. Menu1 is the worksheet which I list or
amend
the
title of the 100 worksheets while the other 3 menus will created
by
using
the
"=" sign.

In menu1, column A is the 100 worksheets title.
Under column B, I had created a macro button for each of the 100
worksheets.

eg A1 Expenses B1 (a macro button which will bring me to the
correct
worksheet)

When I do a sorting under menu1 by selecting column A & B, I had
a
problem
on the other 3 menus. The macro buttons under column B does not
follow
column
A during the sorting.

eg I am in menu3

A1 address but B1 button will direct me to the expenses worksheet

Pls help

tnks