Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link individual sheet to one sheet in another workbook
I'm trying to set up two folders; one group and one individual. The
individual folder needs to link back to it's corresponding worksheet in another workbook/folder. Is there a way to do this without having to fomulate each cell individually? (We're talking about 35-40 pages). Thanks for any/all suggestions |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Link individual sheet to one sheet in another workbook
Hi This is just a suggestion on how to start as I'm not sure if I understand correctly (what do you mean by "folders"?), but try... For multiple files: 1) Creating a list of the file names that you want the links to go to. Have a look at: http://www.erlandsendata.no/english/...ldersscripting 2) Using the indirect function to build the reference to the cell you want to link to (check out the Excel help files) & pasting it down alongside the list of file names. Or for a single file with multiple sheets: 1) Creating a list of sheet names that you want the links to refer to. For this I use the macro below: Code: -------------------- Sub ListOfSheetNames() 'This macro is adapted from http://www.j-walk.com/ss/excel/tips/tip81.htm Dim UserRange As Range Dim output As String Dim prompt As String Dim title As String Dim i As Integer output = "List Of Sheet Names" prompt = "Please select a cell ON THIS SHEET where you want a vertical list " _ & "of the sheet names in the workbook to be placed." _ & "If you are not on the sheet you want the list on, please " _ & "cancel this message box and select the sheet you want before rerunnning the macro." title = "SELECT CELL FOR BEGINNING LIST IN." ' Display the Input Box On Error Resume Next Set UserRange = Application.InputBox(prompt:=prompt, title:=title, _ Type:=8) 'Range selection ' Was the Input Box cancelled? If UserRange Is Nothing Then MsgBox "Macro cancelled, exiting macro without creating list of sheet names." Application.ScreenUpdating = True Exit Sub Else End If 'To format Header cell. With UserRange .Range("A1") = output .Font.FontStyle = "Bold" End With For i = 1 To Sheets.Count UserRange.Offset(i, 0).Value = Sheets(i).Name Next i 'UserRange.Select Application.ScreenUpdating = True MsgBox "Listing of the sheet names for the " & Sheets.Count & " in this workbook is complete.", vbInformation, "LIST IS COMPLETE" End Sub -------------------- 2) Using the indirect function to build the reference to the cell you want to link to (check out the Excel help files) & pasting it down alongside the list of sheet names. hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=521353 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
how do i link from one sheet to another in the same workbook? | Excel Worksheet Functions | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) |