Hyperlink formula and Copy & Pasting the result

[Bullpen]

Hey everyone. I'm hitting my head against a wall and could use your collective help. I need to create hyperlinks to 200 tickets from our company's OSS. I want to put the link to that ticket, via the ticket name, in a spreadsheet. I can't figure out how to copy the results of a hyperlink function into another spreadsheet because the hyperlink function is referencing other cells due to a concatenation. I've pasted a portion of my sheet below and attempted to explain what is in each of my columns below. Any help you can offer is greatly appreciated.

Column A: URL (always the same)
Column B: Ticket #
Column C: Concatenates the URL with the ticket number because combined, that is the full URL to get to each ticket
Column D: Hyperlink formula (eg: =HYPERLINK(C2,B2)

Hyperlinks in Excel Help 2023.04.17.xlsx
	A	B	C	D
1	URL	Ticket #	Concatenate	HyperLink
2	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437782	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437782	1437782
3	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437783	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437783	1437783
4	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437784	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437784	1437784
5	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437785	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437785	1437785
6	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437786	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437786	1437786
7	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437787	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437787	1437787
8	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437788	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437788	1437788
9	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437789	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437789	1437789
10	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437790	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437790	1437790
11	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,	1437791	https://dashboards.fake.domain/sites/ticket-info.aspx?id=14&page=1&param=number,1437791	1437791
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=CONCATENATE(A2,B2)
D2:D11	D2	=HYPERLINK(C2,B2)

I need to be able to take the data in Column D and paste it into another spreadsheet so that when it is pasted, it shows the hyperlinked ticket number.

Thanks in advance.

 

[excel_learnerz]

To achieve this, you can use a combination of VBA and Excel's native features. Here's a step-by-step guide to create hyperlinks for each ticket and then copy them to another worksheet:

1. Press Alt + F11 to open the VBA editor.
2. Click on "Insert" > "Module" to insert a new module.
3. Paste the following code into the module:

Sub CreateHyperlinks()
Dim wsSource As Worksheet, wsDestination As Worksheet
Dim lastRow As Long
Dim i As Long

Set wsSource = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the name of your source worksheet
Set wsDestination = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with the name of your destination worksheet

lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

Application.ScreenUpdating = False

For i = 1 To lastRow
wsSource.Hyperlinks.Add Anchor:=wsSource.Cells(i, "D"), _
Address:=wsSource.Cells(i, "C").Value, _
TextToDisplay:=wsSource.Cells(i, "B").Value
Next i

wsSource.Range("D1:D" & lastRow).Copy
wsDestination.Range("A1").PasteSpecial Paste:=xlPasteAll

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

1. Replace "Sheet1" with the name of your worksheet that contains the URLs and ticket numbers. Replace "Sheet2" with the name of the worksheet where you want to paste the hyperlinked ticket numbers.
2. Close the VBA editor.
3. Press Alt + F8 to open the "Macro" dialog box, select CreateHyperlinks, and click "Run".

 

[Bullpen]

To achieve this, you can use a combination of VBA and Excel's native features. Here's a step-by-step guide to create hyperlinks for each ticket and then copy them to another worksheet:

1. Press Alt + F11 to open the VBA editor.
2. Click on "Insert" > "Module" to insert a new module.
3. Paste the following code into the module:

Sub CreateHyperlinks()
Dim wsSource As Worksheet, wsDestination As Worksheet
Dim lastRow As Long
Dim i As Long

Set wsSource = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the name of your source worksheet
Set wsDestination = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with the name of your destination worksheet

lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

Application.ScreenUpdating = False

For i = 1 To lastRow
wsSource.Hyperlinks.Add Anchor:=wsSource.Cells(i, "D"), _
Address:=wsSource.Cells(i, "C").Value, _
TextToDisplay:=wsSource.Cells(i, "B").Value
Next i

wsSource.Range("D1:D" & lastRow).Copy
wsDestination.Range("A1").PasteSpecial Paste:=xlPasteAll

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

1. Replace "Sheet1" with the name of your worksheet that contains the URLs and ticket numbers. Replace "Sheet2" with the name of the worksheet where you want to paste the hyperlinked ticket numbers.
2. Close the VBA editor.
3. Press Alt + F8 to open the "Macro" dialog box, select CreateHyperlinks, and click "Run".

Thank you @excel_learnerz

However, I'm getting a run-time error '5': Invalid procedure call or argument.

I kept everything on Sheet 1 and just created a Sheet2 so that I could test it.

All that seems to happen is that it changes the header of Column D in cell D1 from saying 'HyperLink' to saying 'Ticket #' and hyperlinking it.

 

[Bullpen]

I was able to solve this by just copying the data in column D, pasting it into Word, then copy if from Word and paste into a different spreadsheet. So much easier!!