Copy hyperlink VBA in Excel

I wish to copy the hyperlink from one excel file to another excel file where I am collecting all data of the same process. I need to do this using Excel VBA.
Sanjay GandhiFounder, KenhalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rodney EndrigaData AnalystCommented:
You can copy and paste the following code into a module in VBE:

Sub EE_CopyHyperlink()
Dim wbk1 As Workbook, wbk2 As Workbook, sht1 As Worksheet, sht2 As Worksheet, rng1 As Range
Application.ScreenUpdating = False
Set wbk1 = Workbooks("Book1.xlsx")
Set wbk2 = Workbooks("Book2.xlsx")
Set sht1 = wbk1.Sheets("Sheet1")
Set sht2 = wbk2.Sheets("Sheet1")
Set rng1 = sht1.Range("A1:A" & sht1.UsedRange.Rows.Count)

wbk1.Activate
For Each Row In rng1
    If Row.Value <> "" Then
        Row.Copy
        sht2.Activate
        Range("A" & sht2.UsedRange.Rows.Count + 50).End(xlUp).Offset(1, 0).Select
        sht2.Paste
    End If
    sht1.Activate
Next
Application.ScreenUpdating = True
End Sub

You will need to adjust the variable names (workbook names, sheet names) to your specific files. But this code will copy the hyperlinks from data in Column A and paste the information in your 2nd Workbook.

Let me know if this works.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sanjay GandhiFounder, KenhalAuthor Commented:
Yes, it works to copy the link. But, following is the error message I get when I click on the link once the copy is made:

"Cannot open the specified file"

Need further solution.
Rodney EndrigaData AnalystCommented:
Can you check to see if the location of the file exists? Make sure to see if the link is referring to the proper location.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Sanjay GandhiFounder, KenhalAuthor Commented:
The link works ok in the source location. I've checked, it works fine. But after copying it doesn't.
Rodney EndrigaData AnalystCommented:
You can try to use the tips in this article:

http://support.microsoft.com/kb/211503
Sanjay GandhiFounder, KenhalAuthor Commented:
The location and address of files was ok. But there must have been some other clause causing a problem. However, the solution given by you is good.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.