Technically, the sheets in your workbook don't contain "links".
They're simple formulas.
If you have actual links (which is short for "hyperlink") then the macro will
have to be modified.
There may be a better way of doing this, but this was the first that came to
mind.
Since I don't know what your actual workbook looks like,
and I don't know how you wanted the results presented,
I made some allowances for potential configuration differences.
This macro assumes a worksheet called "Summary" in which to summarize the
workbook.
BTW: You'll have to save your workbook in .xlsb (or .xlsm) format in order to
save the macro.
Sub Summarize()
Dim sht As Worksheet
Dim R As Integer, C As Integer, C2 As Integer
Dim FlagDup As Boolean
Dim rng
Dim tString As String
R = 1
Sheets("Summary").Select
Sheets("Summary").Range("A1").Value = "Sheet"
Sheets("Summary").Range("B1").Value = "References"
ActiveSheet.Range("A2:Z65000").ClearContents
'---------------------------------------------------------
For Each sht In Sheets
If (sht.Name <> ActiveSheet.Name) Then
R = R + 1
Sheets("Summary").Cells(R, "A").Value = sht.Name
C = 1
For Each rng In sht.UsedRange
If ((InStr(1, rng.Formula, "=") > 0) _
And (InStr(1, rng.Formula, "!") > 0)) Then
tString = rng.Formula
tString = Left(tString, InStr(1, tString, "!") - 1)
tString = Replace(tString, "=", "")
FlagDup = False
For C2 = 1 To C
If (Sheets("Summary").Cells(R, C2).Value = tString) Then
FlagDup = True
Exit For
End If
Next C2
If (Not FlagDup) Then
C = C + 1
Sheets("Summary").Cells(R, C).Value = tString
End If
End If
Next rng
End If
Next sht
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: KAUSHIK SAVLA <[email protected]>
>To: [email protected]
>Sent: Wednesday, October 15, 2014 3:47 AM
>Subject: $$Excel-Macros$$ VBA Required - For links in worksheet
>
>
>
>Hi Team,
>
>
>Need your help in formulating a macro in the attached file which will do the
>following:-
>
>
>1. there are many worksheets in the attached file
>2. In the attached file, in each sheet data is coming from link in another
>worksheet.
>3. What I want to do is - I want one sheet to be inserted. In that sheet it
>should have name of all the tabs. And against each name of tab in the inserted
>sheet it should have name of the tab from which data is flowing in the tab.
>
>
>For example:-
>
>
>Sheet1 - In this sheet data is coming from tab Sheet2. I want sheet 2 to be
>shown against Sheet 1.
>
>
>Sheet2 - In this sheet data is coming from tab Sheet3 and Sheet4. I want
>sheet3, Sheet4 against Sheet 2.
>
>
>Sheet3 - In this sheet data is coming from tab Sheet4, Sheet5 and Sheet7. I
>want Sheet4, Sheet5 and Sheet7 against Sheet3.
>
>
>And so on............................
>
>
>Please help.
>
>--
>
>Warm Regards,
>Kaushik Savla
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to [email protected].
>To post to this group, send email to [email protected].
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
