Alternative to convert formulas to values that keeps hyperlinks active?

[SingCell]

I've searched for some VBA solutions to this but I haven't found one that suits my needs. I'm using Excel 2007 and I'm a VBA novice.

Problem: The macro will be assigned to a command button and will be used by laypersons when they finish filling in data on a worksheet in Workbook 1. The sheet contains maybe 30 columns and 50 rows with a mix of fixed values and values generated by Vlookup and Indirect formulas. I need to copy the sheet from Workbook 1 to Workbook 2. Workbook 2 will be for archival purposes so I want to convert all formulas to fixed values. The catch is dealing with some cells that contain hyperlinks to PDF files...

Current Solution: I currently do this with a macro that moves/copies the sheet from Workbook 1 to Workbook 2, it then selects all cells in the new sheet in Workbook 2, copies all cells, then pastes-special "as values" to the exact same cell locations. This works great for me since the cell formatting and data in the sheet are VERY irregular and I have merged cells all over the place. This method keeps the exact formatting I need maintain:

ActiveSheet.Copy After:=Workbooks("Workbook2.xlsx").Sheets(1)
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

The problem is that a few of the cells have hyperlinks with "friendly names" and I lose the hyperlinks when I convert to values. The hyperlinks are not inserted directly, they are created by a formula, =HYPERLINK("N:\Filepath\"&C16&".PDF", "Click_For_PDF"), and the row and column that contains the hyper link will vary for each sheet I want to migrate from Workbook 1 to Workbook 2 using this macro. I want to keep the hyperlink active with the clickable friendly name in Workbook 2.

Possible Solution: I'm open to all types of solutions, but is there a way to essentially use my existing macro but AFTER converting to values with paste-special, go back to the original sheet in Workbook 1 that still contains formulas (or maybe a temporary duplicate sheet I migrate to Workbook 2?), search for all cells with a "value" of "Click_For_PDF", copy ONLY those cells and paste (normal) into the corresponding cell locations in the sheet in Workbook 2 that now contains fixed values? ALL of my hyperlinks have the friendly name "Click_For_PDF" so it should be an easy way to identify the hyperlink cells. The cell location of the hyperlink copied in Workbook 1 needs to carry over to Workbook 2 and I said before, the row and col vary with every sheet I want to archive with this macro.

Open to all options that fit the needs,

Many thanks!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Here's one approach...

Prior to Copy-PasteValues, do a Find-Replace step to convert the Hyperlink formulas to constants.
Find: "=HYPERLINK", Replace with: " '=HYPERLINK" (apostrophe added)

After Pasting Values, simply reverse the process:
Find: " '=HYPERLINK", Replace with: "=HYPERLINK" (apostrophe removed)

 

[SingCell]

Thanks for your reply Jerry,

Is this what you mean?

ActiveSheet.Copy After:=Workbooks("Workbook2.xlsx").Sheets(1)
ActiveSheet.Cells.Select
Find: "=HYPERLINK", Replace with: " '=HYPERLINK"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Find: " '=HYPERLINK", Replace with: "=HYPERLINK"


On the "=HYPERLINK" I get a Compile error: Expected:line number or statement or end of statement. Is there a syntax problem?

Thanks!

 

[Jerry Sullivan]

My find replace comment wasn't a literal code example but rather a suggested approach.

Here's a code example you can try....

Sub PasteValuesRetainHyperlinks()
    ActiveSheet.Copy After:=Workbooks("Workbook2.xlsx").Sheets(1)
    
    With ActiveSheet.Cells
        .Replace What:="=HYPERLINK", Replacement:=" '=HYPERLINK", _
            LookAt:=xlPart, SearchFormat:=False
        .Copy
        .PasteSpecial (xlPasteValues)
        .Replace What:=" '=HYPERLINK", Replacement:="=HYPERLINK"
        .Range("A1").Select
    End With
    Application.CutCopyMode = False
End Sub

Note that there is a space character in front of the single quote so that the Find-Replace doesn't treat the leading single quote as special character.

 

[SingCell]

Yeah, sorry. Maybe I should have said VBA rookie instead of novice in my opener. I get your approach but I don't know how to code it.

The approach is exactly what I'm looking for. If I manually add apostrophe in front of all my hyperlinks and then run my original macro, it protects the hyperlink formulas. I just have to go back an manually remove the apostrophe. Perfect! This is the overall result I'm looking for.

But the code you gave me is not doing this. The end result is that only the first hyperlink in the sheet is converted to a value with an apostrophe [ 'Click_for_PDF ] and all subsequent hyperlinks have simply been converted to values [ Click_for_PDF ] as with my original macro. All hyperlinks have been removed.

It seems like it is doing the replace after the paste as values and only doing it once rather than all occurrences?

The approach is perfect. Any idea how to change the code to get the result I'm looking for?

Thanks for your help!

 

[Jerry Sullivan]

Hmmm...I don't know why that isn't working for you.
Did you copy and paste the code exactly with no modifications?

It sounds like the first .Replace is not getting applied to all the cells in the newly copied sheet.

Try running the code from the VB Editor using the F8 key to step through 1 line at a time and watch what happens in the workbook.

 

[Jerry Sullivan]

Another possibility is that the cells have already been pasted as values before the sheet is copied to the other workbook.

 

[SingCell]

I think I figured out the problem. The hyperlink formulas are in a merged cell taking up three columns. I guess this is why the replace function wasn't finding them. I moved a hyper link to an unmerged cell and the macro works perfectly, hyperlinks are active.

If possible, I would like to keep the hyperlinks in the merged cell as it would mess up the formatting of my sheet if I either unmerge the cells or move the hyperlink to another portion of the worksheet. Is there a way to make the replace function recognize merged cells or is my only option to change formatting?

Also, my previous statement that the apostrophe only adding to the first hyperlink was wrong...I had forgotten to remove it from my manual attempts. ALL hyperlinks were getting skipped by the replace function. Sorry for the confusion.

Thanks again.

 

[Jerry Sullivan]

Ahhh that explains it. This is another good reason to try to avoid using merged cells unless there is no alternative.

While other VBA techniques could be used to execute the same approach of Convert to Constant -> Revert to Formula, I'd really encourage you to first look for an alternative to merged cells that meets your needs.

You say that your merged cells are spanning columns. If you want the text to be centered across those cells, try using the Alignment option "Center across selection".

 

[SingCell]

Ok, thanks! Unfortunately, the "center across selection" option keeps the clickable region of the hyperlink in a single cell rather than with the text itself but I should be able to work with this.

This solves my main problem of retaining hyperlinks while converting to values. Thank you so much for your help!