reference in hyperlink, delete reference without losing hyperlink

Hopefully I'm not crossposting this too much. 3rd site, hopefully answers soon. This is really making my head hurt, so just I'm copypasting it from my 7forums post...


Okay, this is a bit strange and I can't think of any way this could work, but maybe the internet can surprise me...

I have data (text, not numbers) in Sheet1. I concatenate it into Sheet2 as part of a hyperlink. How can I make it so that the whole workbook is only Sheet2 with working hyperlinks? (Yes, I have a practical application of this)

Example:
Sheet1!A1 says: page
Sheet2!A1 says: =HYPERLINK(CONCATENATE("http://www.com/",Sheet1!A1),Sheet1!A1)
Sheet2!A1 actually reads: "page", and it has a hyperlink to "http://www.com/page"

Now, how can I condense the workbook to ONLY Sheet2, maintaining not only the text referenced from Sheet1, but the working hyperlinks as well?
So the workbook will have ONE page with cell A1 reading "page" and hyperlinking to "http://www.com/page"

Does that make sense?
Is it even possible?


Edit: If this is impossible, please let me know so I can focus on a different approach.

Sorry bout that, new. Here they are (links to cross posts):
http://www.sevenforums.com/microsoft...ible-task.html
http://answers.yahoo.com/question/in...522AAfEPcC&r=w

Why not just store the Sheet1 values on Sheet2 ?

If you want to replace the HYPERLINK function calls with static links then you will need code to iterate and replace etc...

Public Sub Example()
    Dim rngC As Range
    Dim lngEndPos As Long
    Dim strURL As String, strFriendly As String
    For Each rngC In Sheets("Sheet2").Columns("A").SpecialCells(xlCellTypeFormulas).Cells
        With rngC
            If UCase(Left(.Formula, 11)) = "=HYPERLINK(" Then
                lngEndPos = InStrRev(.Formula, ",")
                strURL = .Parent.Evaluate(Mid(.Formula, 12, lngEndPos - 12))
                strFriendly = .Text
                .Clear
                .Hyperlinks.Add .Cells(1), strURL, , , strFriendly
            End If
        End With
    Next rngC
End Sub

Awesome! Only thing, how do I apply it to the whole sheet at once, instead of just column A (not great with vba)?
What do I replace the column part of the code with?

For Each rngC In Sheets("Sheet2").Columns("A").SpecialCells(xlCellTypeFormulas).Cells

Remove the Columns("A") reference and replace with Cells

Note: there are limitations to the number of range Areas that SpecialCells can process, see: here for more info. / workaround etc.

Please be sure to update all of your cross posts accordingly.