Hyperlink formatting

[BrWolv]

Hi All,
I am a noob here, so please forgive my laymens terms. I am currently working on a few massive workbooks. Each workbook will contain approx. 100 or so Worksheets, With a Master Worksheet for totals, datalist, etc..

I am working on the first WB & I am adding Hyperlinks on both the Master Sheet & the individual sheets (because who wants to scroll through 100 tabs, back and forth?) I have completed approx. 20 Sheets, and thought "there has to be a better way, than doing it 1 by 1", so the question (using 2010 BTW);

Is there any option with some type of format painter, or paste option that would allow me to do multiple cells at once? Or any suggestions at all to help speed up the process?
My master sheet has 2 columns, each 52 rows long. each of those 104 cells need to link to the respective worksheet within the same workbook.
i.e., cell A1 links to Worksheet 2, Cell A2 Links to Worksheet 3, and so on... Any help would be greatly appreciated (again, if i am not providing enough information, forgive me!

 

[jeffreybrown]

Here's an example maybe you can get to work. It actually create's a Index sheet and put's the hyperlinks there.

Sub IDX()
    Dim ws As Worksheet, i As Integer
    Worksheets.Add(Before:=Worksheets(1)).Name = "Index"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Index" Then
            i = i + 1
            Sheets("Index").Range("A" & i).Value = ws.Name
            Sheets("Index").Hyperlinks.Add _
            Anchor:=Range("A" & i), Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        End If
    Next ws
    Sheets("Index").Columns("A").AutoFit
End Sub

 

[BrWolv]

Thanks Jeff! Ill see if i can figure out how to get that to work. My VBA skillset is pretty limited, however, i have got lucky in the past. Another shortcut I was hoping for is a way to edit the "ScreenTips" for all links at the same time, meaning all of the Hyperlinks on the Master Sheet will have the same "ScreenTip", the tip on each Worksheet will be the same as well (Click to Link to Master). Is it possible to enter edit all ScreeTips @ once?

thanks again

 

[jeffreybrown]

How about this...

Sub IDX()
    Dim ws As Worksheet, i As Integer
    Worksheets.Add(Before:=Worksheets(1)).Name = "Index"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Index" Then
            i = i + 1
            Sheets("Index").Range("A" & i).Value = ws.Name
            Sheets("Index").Hyperlinks.Add _
            ScreenTip:="Click to Link to Master", _
            Anchor:=Range("A" & i), Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", _
            TextToDisplay:=ws.Name
        End If
    Next ws
    Sheets("Index").Columns("A").AutoFit
End Sub

How about this for some extra help with macro's...

http://www.rondebruin.nl/code.htm

 

[BrWolv]

Thank you, Ill give this a try!!