Macro to hyperlink and copy/paste value to 2nd worksheet???

I need some help and have been looking through these forum all day.

I have 2 worksheets. "One" is a simple list of names. "Two" is more of a database in which each row from "One" represents a page on "Two" (30cells). its a list f names and sort of a progress report. I need to link the name on "One" to the persons progress report on "Two".
I found a macro online that allowed me to copy info from "One" to "Two" to the correct location. (See: "One"A2->"Two"A34, "One"A3->"Two"A64, "One"A4->"Two"A94)

This is that macro:

Sub Test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lngRowCounter As Long
Dim lngRowMultiplier As Long


lngRowCounter = 1
lngRowMultiplier = 30

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Do While Not IsEmpty(ws1.Range("A" & lngRowCounter))
ws2.Range("A" & lngRowCounter * lngRowMultiplier +4).Value = _
ws1.Range("A" & lngRowCounter).Value
lngRowCounter = lngRowCounter + 1
Loop

End Sub

What I would really like though is to be able to do the above AND create a hyperlink in "One" that corresponds to the correct location on "Two". If I can get both functions in one macro that's be perfect.

Or a macro that will allow me to hyperlink "One"Column A(1-1000) to every 30th row starting at row 4 on "Two"

Or if there's a separate macro I can run that will allow me to hyperlink all values in "One" to like values in "Two" that'd be awesome as well.

I'd even be willing to settle with a formula.
I played around with the following formula thinking I could just drag down but couldn't get it to work
=HYPERLINK("Two!A") & (ROW() *30+4))

I have no experience with Macros outside of the googling I did today, so any insight you can provide would be much appreciated. I just cant se myself doing the right click add hyperlink for every row on "One"... Help? Please?

hi AudreaBull, sort of that basing on the code provided:

Please Login or Register  to view this content.

PS. You need to use HASH sign on message edit panel while posting any code on the forum