Rookie here. I am trying to figure out a way to automatically insert a hyperlink based on a vlookup value - OR - keep a hyperlink created through "=hyperlink" when pasting as values (via an existing macro).
I'm attempting to automate a form from an existing report, so it's a bit messy, but my current formula bar is:
- Code: Select all
=HYPERLINK(VLOOKUP("Website:",'Submitted Proposal'!$A:$ZZ,ROW(A2),FALSE),VLOOKUP("Name:",'Submitted Proposal'!$A:$ZZ,ROW(A2),FALSE)&CHAR(10)&CHAR(10)&VLOOKUP("Total Guest Rooms:",'Submitted Proposal'!$A:$ZZ,ROW(A2),FALSE)&" Total Rooms"&CHAR(10)&CHAR(10)&VLOOKUP("Website:",'Submitted Proposal'!$A:$ZZ,ROW(A2),FALSE)))
Which outputs as:
Name: HOTEL NAME
Total Guest Rooms: # OF ROOMS
WEB URL
**
With the formula above, the entire cell hyperlinks to "Web URL", which I extracted from the original report.
The problem happens when I use a macro to paste the entire spreadsheet as values to another sheet so my colleagues can edit the text, add bold to the Hotel name, etc.
Unfortunately the pasting as values aspect is important.
**
I'm wondering if there's a way to keep the hyperlink value without the formula during the paste as values, or if there's a VBA code to insert hyperlink address based on a vlookup value (which I would do after the paste as values is complete).
While it would be simple enough for them to just right-click, insert hyperlink, I need to make the report as automated as possible.
Any help or ideas you can provide would be greatly appreciated.
This is my current paste-as-values code:
- Code: Select all
Sheets("Sheet of Crazy Formulas").Range("A:H").Copy
With Sheets("Final Grid").Range("A:H").End(xlToLeft).Offset(, 0)
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
Thank you so much for your help in advance.
--Stephanie