How to name a table with reference to sheets

[siddiq1212]

Dear all,
I have five sheets having same tables and different values.
I want to have a summary in which i am giving link to that tables of different sheets.
I want to names these table with sheet so that when i click i summary i will got to know from where this value came.

Important : When i will copy and insert new sheet the future it tables names must come with table sheet inserted.
Can any body explain how to do it.

[newdoverman]

Create the tables on the various sheets then go to the Formulas tab, Name Manager, select each table and rename it to suit your situation.

[siddiq1212]

I want to do it with sheet name automatically.
Is it possible or not?

[newdoverman]

I think that someone could write a macro that will do that but it is out of my area of interest/ability.

[Jarko28]

Hi siddiq1212,

Assuming you can have 1 cell within each sheet empty, I chose cell "A1" on each sheet
...and assuming that you table is in range "B1:C10" for demonstration purposes...

You can make a copy of the workbook you have, open the copy, create a new sheet named: Index

After you have created the sheet, you need to go into the VBA editor by pressing Alt+F11

Click on the Index Sheet Module. In the Index Sheet Module, paste in the following code:

Please Login or Register  to view this content.

Each time you make a new sheet and rename it, you must click on the Index sheet. Once you click the Index Tab, what it will do is create a new named range based on the sheet name. It will also add a hyperlink to that sheet.

I am assuming that you want to do this for all sheets in the workbook except the Index sheet which is used as a summary of all your sheets which you can hyperlink to.

I have also attached a workbook sample.

Thanks.