sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
sorting with macro button
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 |
All times are GMT +1. The time now is 02:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com