DH Templates

excel tab (worksheet ) cell name help!?

Hi guys i have a workbook with about 20 tabs of diffent invoice numbers, what i want to do is create a new worksheet which A) List all the tabs ( by the tab name ) B) Links the tab ( so when i click on a cell) that tab automatically pops open. C) If possible, I would like another coulum that gives me ( Invoice amount) where it automatically gets the info from a certain cell in all tabs( ALL TABS ARE IDENTICAL) ( Invoice template) So basically Colum A ( List of tabs names that link ) and Colum B List of Total amounts ( a particalar cell in tab) Is this possible to do without having to type EVERY Tab name? and =(everysheetcell) manually ? Please help VBAXLMAN IM WAITING FOR UR AWSOME ANSWER ALSO <3 ALL EXCEL PROGRAMER!

Public Comments

1. Here is one way, based on Excel 2003. Open View>Toolbars>Forms. Click on the 'Button' and click in the worksheet. Cancel the Assign Macro window. Drag and size the button to your preference. It will be labeled Button 1. You can rename it by right clicking it, then left clicking it to edit text. Next click Tools>Macro>Record New Macro. Name the macro 'To Sheet Whatever'. Next, click on the tab for the that sheet in the workbook. Then, click Tools>Macros>Stop Recording. Now, go back to the sheet where Button 1 is, right click on it and 'Assign Macro'. Select the macro you just created. Now, click the button and it will take you to the sheet in question. Bear in mind you probably will want to set a button on each sheet to take you back to the master sheet. But, it will take only one macro that you can apply to all buttons. You can copy and paste buttons, rename them, and assign different macros to them to speed up the process. Repeat this process for each sheet and you will have 20 buttons in your new worksheet, each labeled with the sheet that button will open, if pressed. If you want to clean up the workbook, you can hide the tabs by removing the check mark in Tools>Options>Sheet Tabs. For the value fields in your master worksheet, select a cell you want the data to appear in. If your first sheet is now tabbed First Sheet, then the formula to bring data from that sheet into your master sheet in that cell is: =FirstSheet!A3 (or whatever the cell is that contains the data). Repeat the process for all 20 sheets.
2. When summary sheet is on, right click on its tab and choose view code, paste the following code to the right blank area Set the 2 address ranges invoiceTotalOnOtherSheet(demo assume is cell A10 on other sheet(s)) and sheetColumnRange(where sheet name populate for you to click) , then you're ready to go. sheetColumnRange is click sensitive range where sheet names populate, it can be on any place besides column A and invoice total always on right adjacent cell of sheet name. Dim sheetColumnRange Private Sub Worksheet_Activate() invoiceTotalOnOtherSheet = "A10" 'since same on each sheet, so address of single cell only sheetColumnRange = "A4:A43" 'could be A4:A43 if you expect to have another 40 sheets on this workbook stCol = Range(sheetColumnRange ).Cells(1).Column stRow = Range(sheetColumnRange ).Cells(1).Row For a = 1 To Worksheets.Count    If Sheets(a).Name <> ActiveSheet.Name Then       Cells(stRow, stCol).Value = Sheets(a).Name       Cells(stRow, stCol + 1).Value = Sheets( Sheets(a).Name ).Range( invoiceTotalOnOtherSheet ).Value       stRow = stRow + 1    End If Next End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range)    If Target.Cells.Count > 1 Then Exit Sub    If Target = "" Then Exit Sub    If Not Intersect(Target, Range(sheetColumnRange)) Is Nothing Then Sheets(Target.Value).Activate End Sub Please contact for more details