Search code examples
excelcopy-pastehardcodedvba

paste values are hardcoded in macro

Firstly my desired result is to concatenate two cells to create a hyperlink. One cell has the web address of our ticket management system and the other has the ticket number. The two cells exist on the DrillDown sheet of a double clicked Pivot Table, the source data of which does have the hyperlink already, but it doesn't pull through to the drilldown sheet and as I've read on multiple forums it's not possible to make it pull through.

So I recorded a macro of of the concatenate and then used paste values of the concatenate to another cell and did a carriage return and it made it a Hyperlink. Boom! Job done?

No, when I tested the recorded macro on some other data, it did all the concatenating correctly but when it got to the pasting values bit, it pasted the values of the previous data i was working on?

Is there a way in VBA to get it to copy and paste the value of the selected cell, not what has been hardcoded during the recording of the macro?

Apologies if this is bleeding obvious, but I am no VBA expert... far from it.

Sub CreateHyperlink()
'
' CreateHyperlink Macro
'

'
Columns("A:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],5)"
Range("A3").Select
ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(""=HYPERLINK(""""http://test.analytics.com/workspace/379/item/"",RC4,"""""""","","","" "","""""""",RC5,"""""""","")"")"
Range("A4").Select
Columns("A:A").EntireColumn.AutoFit
Range("B3").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],LEN(RC[-1])-0)"
Range("B3").Select
Columns("B:B").EntireColumn.AutoFit
Range("B3").Select
Selection.Copy
Range("C3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("B3").Select
Selection.Copy
Range("C3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("B3").Select
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
    "=HYPERLINK(""http://test.analytics.com/workspace/379/item/55949"", ""INQ-55949"")"
Range("C4").Select
Columns("C:C").EntireColumn.AutoFit
End Sub

Thanks J

Solution

  • Things can be simplified a bit:

    Sub CreateHyperlink()
'
' CreateHyperlink Macro
'

' Move everything four columns to the right
Columns("A:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Create the hyperlink by adding the last 5 characters of the ticket number and then use the full ticket number. The full ticket number is in cell E3
Range("C3").FormulaR1C1 = _
     "=HYPERLINK(CONCATENATE(""http://test.analytics.com/workspace/379/item/"",RIGHT(RC[2],5)),RC[2])
Range("C4").Select
Columns("C:C").EntireColumn.AutoFit

End Sub

    I've discarded all of the intermediate columns and almost all of the selecting and copying. This formula just concatenates the last 5 characters in E3 on to the end of the URL and uses the full text of E3 as the link text to be clicked on