PDA

View Full Version : Solved: add sheet with hyperlink



benong
10-05-2010, 08:07 PM
hi,
i have data in sheet1 column A.
i want to add worksheets(sheet2, sheet3,...) based on the data found in sheet1 column A.
The added worksheet name should be the same as each data row in sheet1 column A.
I want to add hyperlink from sheet1 column A data to each added worksheets for easy navigation.
Does anyone know how to accomplish this automatically?
Many thanks for guidance.

mbarron
10-05-2010, 08:32 PM
Try this:

Sub LinkTo()
Dim i As Long, sFirst As Worksheet
i = 2
Set sFirst = ActiveSheet
With sFirst
Do Until .Cells(i, 1) = ""
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = .Cells(i, 1)
.Cells(i, 1).Hyperlinks.Add Anchor:=.Cells(i, 1), Address:="", _
SubAddress:="'" & .Cells(i, 1) & "'!A1"
i = i + 1
Loop
End With
sFirst.Activate
End Sub

benong
10-05-2010, 09:00 PM
dear mbarron,
it works like magic.
i 've been messing around for nearly 2 hours but still cannot get it to work.
yours is so neat and simple.
many thanks :)

benong
10-10-2010, 06:06 PM
strange,
it was working fine few days ago and it is now not working?!!
the error come from this line:
Sheets.Add after:=Sheets(Sheets.Count)

run time error 1004
method "add" of object sheets failed

Pls advise.