List of Internal links

I've inherited a workbook that at present count has 57 tabs, each of which has hundreds of links to other worksheets in the workbook. I'm trying to make sense of it all and bring some order to it, but it's a mess. What would help me out is to get a list of all the formulas that reference other sheets. Ideally I'd want a single sheet with 3 columns, the first showing the formula, the second showing what sheet the formula's in, and the 3rd showing what cell in that sheet the formula comes from.

I've looked and can't find much for hunting down internal links. The below macro is one I copied a long time ago and never fooled with. It's got a start, but I'm lost on how to expand it to bring in all the values I need and consolidate it into a single worksheet. Any help would be highly appreciated.

Please Login or Register  to view this content.

Well, I thought about it, and found a solution. I turned a macro for finding all formulas in a workbook into one that whittles it down to JUST the internal links. Code is below:

Please Login or Register  to view this content.

Shorter, but upon running I get a run-time error of 1004 saying "no cells were found" on the line:

Please Login or Register  to view this content.

Hi again,

Many thanks for this very useful feedback - it's always interesting to see the "tweaks" that are applied afterwards.

You're very welcome - glad I was able to help.

Best regards,

Greg M

Hi again,

I've had a closer look at your code and can suggest just a small improvement.

There's quite a bit of code nested between the following "On Error resume Next - On Error GoTo 0" statements:

Please Login or Register  to view this content.

In general, you should always try to reduce the amount of such code to an absolute minimum. The following version helps to do this:

Please Login or Register  to view this content.

Just for information, I've also included alternative code for creating/clearing the Formulas worksheet.

Hope this might be of some use or interest to you, and thanks again for your feedback.

Regards,

Greg M

snb,
I really enjoy how you can take something complex and whittle it down to only a very few names. I gave your macro a whirl, and ran into a couple of snags. I think the following code is to check if sheet "inventory" exists, and if not to add it, or if it does exist write the information to it.

[CODE][/If [iserr(isref(inventory!A1))]CODE]
If no sheet "inventory" exists, the macro does nothing. Walking through it, it skips over the "Sheets.Add.name = "inventory"" line and goes to the "Resize" line, but since no inventory sheet exists nothing happens.

IF an "inventory" sheet DOES exist, the code works fine, except that it places the formula, not the formula with an apostrophe in front of it.

I don't understand the "If [iserr(isref(inventory!A1))]" enough to be able to figure out the problem, so I hope you can explain.

Thanks,
John

Greg,
Thanks for your suggested improvements. I never thought about reducing the clutter between error checking statements, but that makes sense to me. I like the "ClearContents" rather than "Delete"; that way if I have any fancy formatting set up I shouldn't lose it.

Thanks for all your help!

Okay, I think I've got it this time. Seems the problem was "iserr". I replaced with "not", and it appears to be working fine. I also tweaked it to add my headings and autofit my columns.

Please Login or Register  to view this content.