Identify link location

  • Thread starter Thread starter igor
  • Start date Start date
I

igor

Hi,

I have a spreadsheet that has multiple links. Is there a
way to create a different sheet that will display the list
of links in the following format:

SheetName-Cell(Address)- Full Link(cell value)

thank you for your help
 
Are you talking about external links/references in cells?

If so, just loop through all your cells and see if they contain brackets
([ ]) which would indicate a reference to another workbook. You could then
put that information in another sheet

Dim sh as Worksheet
Dim rw as Long
Dim rng as Range
Dim cell as Range
rw = 1
for each sh in thisworkbook.worksheets
if sh.Name <> "Report" then
set rng = Nothing
on Error Resume Next
set rng = sh.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
for each cell in rng
if instr(cell.Formula,"[") then
worksheets("Report1").Cells(rw,1).Value = _
sh.Name & " - " & cell.Address & " - " _
& cell.Formula
rw = rw + 1
End If
Next
End if
End if
Next
 
Tom,

You are the best.

Thank you
-----Original Message-----
Are you talking about external links/references in cells?

If so, just loop through all your cells and see if they contain brackets
([ ]) which would indicate a reference to another workbook. You could then
put that information in another sheet

Dim sh as Worksheet
Dim rw as Long
Dim rng as Range
Dim cell as Range
rw = 1
for each sh in thisworkbook.worksheets
if sh.Name <> "Report" then
set rng = Nothing
on Error Resume Next
set rng = sh.UsedRange.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
for each cell in rng
if instr(cell.Formula,"[") then
worksheets("Report1").Cells(rw,1).Value = _
sh.Name & " - " & cell.Address & " - " _
& cell.Formula
rw = rw + 1
End If
Next
End if
End if
Next

--
Regards,
Tom Ogilvy


igor said:
Hi,

I have a spreadsheet that has multiple links. Is there a
way to create a different sheet that will display the list
of links in the following format:

SheetName-Cell(Address)- Full Link(cell value)

thank you for your help
Click to expand...


.
Click to expand...
 
Back
Top