Mass excel hyperlinking

Microsoft Office 2010 professional
September 23, 2010 at 15:15:10
Specs: Windows 7, Xeon W3520 / 8GB ram
I need to link rows 201-500 to the correct sheet number, I've already made 201-500 as a separate sheet, I just need to hyper link it so if you click number 201 on the index it will direct you straight to sheet 201. I have to do this on 2 separate sheets so I have to do it a total of 600 times. doing this manually would take many hours of a very repetitive thing.

here is some pictures to better describe my situation

I can re-enter the list of numbers easily, if that is something required to mass link these easily and fast.


See More: Mass excel hyperlinking

Report •

September 23, 2010 at 15:57:09
I'm a bit confused by your pictures as they relate to the words in your post.

You say you want "click number 201 on the index".

I see 201, etc. in what appears to be Column A, yet I see a bunch of hyperlinks in what appears to be Column C.

Where do you want the hyperlinks you are asking about?

Report •

September 23, 2010 at 16:02:34
There's multiple sheets. It's 1 excel file with about 510 sheets. I have 500 sheets numbered 1-500, I want to hyperlink column D to the excel sheet that matches the number from column A in the same row.

so if A1 is 1, I want to be able to click on the text in D1 and have it take me to the Sheet labeled "1"

Edit: I want to be able to do this fast or all at once, since doing manually will take quite awhile, I have to hyper link a total of about 600 cells

Report •

September 23, 2010 at 17:01:16
re: so if A1 is 1, I want to be able to click on the text in D1 and have it take me to the Sheet labeled "1"

So the numbers in Column A really have nothing to do with the links, other than as a "visual" for the user to know what sheet they will go to if they click in Column D.


How about this:

Sub MakeQuickLinks()
 For shtLink = 1 To Sheets.Count
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(shtLink, "D"), _
    Address:="", SubAddress:="'" & shtLink & "'!Print_Area"
End Sub

Note: This will create the hyperlinks in Column D of the ActiveSheet for every sheet in your workbook. You can modify the start and ending numbers as required.

I suggest you try this in a backup copy of your book because macros cannot be easily undone.

This code will remove all hyperlinks in Column D if things go badly with the other code.

Sub DeleteColDLinks()
End Sub

Report •

Related Solutions

Ask Question