Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro Question - Hyperlink Issue

0

Hello,

I created a Macro to do some simple formatting and formulas which all works great, BUT the more complicated part is that Macro also takes a Hyperlink formula into Word and pastes it, then back into the Excel Workbook so it is no longer a formula and now instead a hyperlink connected to the name. When I read through it everything looks good and there shouldn't be any issues. I would way 10% of the time it comes back with Ref instead of the name meaning somehow the pasted content never made it back into Excel. 

I feel like I am missing something here and don't know what it is. 

Could someone PLEASE help me I would greatly appreciate it! 

VBA Code Attached. (Please let me know if you need anything else from me)

All the best, 

Quinn3110

Answer
Discuss

Discussion

Hi Kyle and welcome to the Forum. 

I can't answer now but using Word just to copy the hyperlinks isn't a great idea (but your code looks like you've made extensive use of the macro recorder).

I'll try to answer tomorrow, showing how to avoid  #REF! errors hopefully. 
John_Ru (rep: 6102) May 4, '22 at 1:56 pm
Hey John, Thank you so much for your reply. 

I use the record function where I can. I know the Macro is probably a mess to someone with your knowledge, so I apologize for the mess and greatly appreciate the help!
Quinn3110 May 5, '22 at 11:26 am
Add to Discussion

Answers

0

 Kyle

Your code is overlong (since you used the macro recorder) so I haven't pasted it below but commented within the code in the revised file attached.

I've eliminated the use of the Word application in the macro (commented out) and made the change below to replace the HYPERLINK formula with a loop which adds the full name and hyperlink to column D:

' ### Line commented out...
    'Range("D2:D" & lRow).Formula = "=HYPERLINK(RC[8],RC[-1])"

' ### ... and replaced by loop of column D
For n = 2 To lRow
    With Cells(n, 4)
        ' add name from cell to left
        .Value = .Offset(0, -1).Value
        ' and hyperlink from 8 columns right
        lnk = .Offset(0, 8).Text
        .Hyperlinks.Add Anchor:=Range(.Address), Address:=lnk
    End With
Next n

I did that since the HYPERLINK formula can be a problem if the reference is in  the first column but refers to a column to the left (which doesn't exist).

I've left copying that column/ pasting to E and elsewhere (inefficient though that is) since I don't have the time or interest to rewrite the whole lot!

At the end of the macro, I've added a line so the resultant file can be saved under a different filename (leaving your "template" file in tact).

' ### added this (so original file isn't overwritten) with suggested file name
Sv = Application.GetSaveAsFilename("Activity_" & Replace(Date, "/", "-"))

There's a suggested filename but you can modify that or pick another to overwrite, as usual).

Hope this fixes thing for you.

Discuss

Discussion

I don't know how to thank you enough... this is a game changer! 

Thank you so very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Quinn3110 May 6, '22 at 11:43 am
That's good Kyle. To thank me, please mark my answer as "Selected" - that tells other users that it works (and adds marginally to your reputation and mine in the Forum)
John_Ru (rep: 6102) May 6, '22 at 11:53 am
Guess you missed my request above :-(
John_Ru (rep: 6102) May 10, '22 at 11:21 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login