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

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