Excel Macro Copy & Paste keep hyperlinks
Dear Experts
I have a macro in excel that copies a row from one tab ("Bio + Ed + Lang") and appends it to the next blank row in another tab ("final output") . This works all fine except where there is a hyperlink involved, the result is just the 'friendly name' and there is no link associated to it which is what I need
The hyperlink in the source row is created by a formula in the cell 'Bio + Ed + Lang'!F2 using [=HYPERLINK(Paste!B13,'Bio + Ed + Lang'!B2)]
The code I have is below, can anyone help?
I have a macro in excel that copies a row from one tab ("Bio + Ed + Lang") and appends it to the next blank row in another tab ("final output") . This works all fine except where there is a hyperlink involved, the result is just the 'friendly name' and there is no link associated to it which is what I need
The hyperlink in the source row is created by a formula in the cell 'Bio + Ed + Lang'!F2 using [=HYPERLINK(Paste!B13,'Bio + Ed + Lang'!B2)]
The code I have is below, can anyone help?
Sub COPYPASTER()
Sheets("Bio + Ed + Lang").Range("A2:G2").Copy
Sheets("Final Output").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
HainKurt
can you attach a sample excel
you can try xlPasteAll
Sub COPYPASTER()
Sheets("Bio + Ed + Lang").Range("A2:G2").Copy
Sheets("Final Output").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End SubASKER
I gave that a go, but alas no joy, I have attached a copy of the spreadsheet withmy own details in it (these are paste from LinkedIn & the URL is manually added into Paste!B13Chrome LinkedIn Writer Dec 2020 with hyperlinks.xlsm
ok whats the problem here?
you want hypher links inside?
you cannot do that! one cell may have one link and it is applied to cell, not any part of the content...
you want hypher links inside?
you cannot do that! one cell may have one link and it is applied to cell, not any part of the content...
ASKER
the problem is not in this cell
it's the one to the left of it which is where the link should go
it's the one to the left of it which is where the link should go
or the issue is on "LinkedIn Link" column?
I replaced all xlPasteValues with xlPasteAll
and got this
I replaced all xlPasteValues with xlPasteAll
and got this
ASKER
ok will check that out - didn't seem to work earlier, but that's more than likely user error as this makes sense
ill add the sheet that I fixed...
29204609.xlsm
29204609.xlsm
ASKER
brilliant, thank you, have had to go out for 20 mins, but will check it out as soon as I get back in- thank you
ASKER
Hi Thanks for this, that seems to only work fine if its the first row of data you paste into the results sheet, try & repeat it and the data goes all over the place (see below). I have tried this out on your example & also in mine with xlPasteValues changed to xlPasteAll - Any ideas?
how do you run again?
are you entering new data,
do you select any cell/row before running
are you clicking the button to run?
when I clear data and run multiple times I get this
same row is added again...
are you entering new data,
do you select any cell/row before running
are you clicking the button to run?
when I clear data and run multiple times I get this
same row is added again...
ASKER
I literally just clicked the button again, looked the results and went back & clicked the button again for the next row
ASKER
just thinking - all the copied cells are derived from formulas, would that throw them off on a paste all?
paste all copies formulas as well
paste values, only copies the values, the one you see on the cell...
I cannot reproduce what you get...
paste values, only copies the values, the one you see on the cell...
I cannot reproduce what you get...
ASKER
ok, no worries, the results I imaged came from the example you kindly sent back, so I'm not sure if we're on different version of excel or something. I'll try to break the code into 2 - ie part one does the paste values for the non hyperlink cells and part two for the cell with a hyperlink and see if that cracks it
I am using Office 2016 :)
maybe that matters...
maybe that matters...
ASKER
cool thanks for the info I'm on Office365's latest version - will try again in the morning when my head is clear. Thank you for your help so far
ASKER
I took a further look at the problem - the issue is that xlPasteAll pastes the formulas, what I need is the values (+ the hyperlink) as I'm effectively building a list of people - Sorry I should have specified this in the original question.
One thing I'm thinking is if I do the paste values & then add in an additional bit of code to add in the hyperlink into the last non-blank cell in column F of the 'Final Output'
One thing I'm thinking is if I do the paste values & then add in an additional bit of code to add in the hyperlink into the last non-blank cell in column F of the 'Final Output'
this may work...
29204609.xlsm
Sub COPYPASTER()
Sheets("Bio + Ed + Lang").Range("A2:G2").Copy
Sheets("Final Output").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Dim LastRow As Integer
LastRow = Sheets("Final Output").Range("A1").CurrentRegion.Rows.Count
Sheets("Final Output").Cells(LastRow, 6).Formula = Sheets("Bio + Ed + Lang").Range("F2").Formula
Application.CutCopyMode = False
End Sub29204609.xlsm
ASKER
Thanks for that, I have literally just finished testing the code below which works - basically it does the normal Paste Values and then goes into the last non-blank cell in Column F & inserts a hyperlink as I cant use formulas in the 'Final Output' - I found that hyperlinks needed to be created in code rather than any form of paste.
Sub COPYPASTER()
'Paste Results (Text Only)
Sheets("Bio + Ed + Lang").Range("A2:G2").SpecialCells(xlCellTypeVisible).Copy
Sheets("Final Output").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Paste hyperlink into last cell in column F
'Check if the link has been entered - if not then delete contents of cell or insert link
If Sheets("Paste").Range("B13") = "" Then
MsgBox "You must add in the LinkedIn Bio link into the Paste Tab"
Dim lastRow1 As Long
Dim FinalCell1 As String
With Sheets("Final Output")
lastRow1 = .Cells(.Rows.Count, "F").End(xlUp).Row
End With
FinalCell1 = "F" & lastRow1
Sheets("Final Output").Range(FinalCell1).Clear
Exit Sub
Else
Dim TextDisplay As String
With Sheets("Bio + Ed + Lang")
TextDisplay = .Range("B2")
End With
Dim LinkAddress As String
With Sheets("Paste")
LinkAddress = .Range("B13")
End With
Dim lastRow As Long
Dim FinalCell As String
With Sheets("Final Output")
lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
End With
FinalCell = "F" & lastRow
With Sheets("Final Output")
.Hyperlinks.Add Anchor:=.Range(FinalCell), _
Address:=LinkAddress, _
TextToDisplay:=TextDisplay
End With
End If
End Sub
ASKER
Thank you so much for your help though
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.