sorting with macro button

[Guest]

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

 

[JW]

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(Before:=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

 

[Guest]

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

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(Before:=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

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

 

[Don Guillett]

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 tab>view
code>copy/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

 

[Guest]

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 tab>view

code>paste , close and ret to excel

when I go to menu1 double click cellA1, no respone

 

[Don Guillett]

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

 

[Guest]

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.

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(Before:=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

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

 

[Guest]

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]

Let's try again.
On the sheet with the sheet names typed into cells on col A. That is where
you right click>select view code>insert 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.

 

[Guest]

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]

Glad to help. Call me Don