PDA

View Full Version : Table of content or Hyperlink for different Table Name in work-book



r_know
07-10-2014, 03:14 AM
Hi to All,

Challenge of Macro world, I have work book which having around 70 sheets (tab) and majorly covered by
"Table" and on that table having many "Pivot Table".

As we know once we create table that having "Table Name" same for "Pivot Table name".

Tables, I linked them by formula/ reference and now in big trouble to find which tab having in a which sheet.

A way to handle drop down from NameBox and several other tricks CTRL + G.

But I think, I need a Table of content or Hyperlink table on one sheet where I can jump to particular table.

Can anyone suggest code or trick to handle my problem?


Regards,RL

werafa
07-17-2014, 03:55 PM
Not quite sure what you are asking for specifically,

but I believe you want an easy way to find the sheet with a specific table or pivot table.

to loop through all sheets, you can use a
dim ws as worksheet
dim wb as workbook

set wb = activeworkbook
for each ws in wb
do something
next ws

you can also use this technique to find each pivottable in worksheet, and then read the pivottable.name property

Jon Peltier has the best resource I have seen for referencing and manipulating pivot tables at
http://peltiertech.com/WordPress/referencing-pivot-table-ranges-in-vba/

Tim

r_know
07-17-2014, 08:41 PM
Thanks for reply!
Let me try .....

my requirement If sheet 1 having tablle name "T_Data"
and his pivot table in sheet 2 "P_T_Data" then

I need on separate sheet Table of content, which says
Table/Pivot Table Location
T_Data Hyperlink of location
P_T_Data Hyperlink of location

It is very hard to do manually where total sheet above 70.

werafa
07-17-2014, 09:06 PM
Loop through all sheets in workbook,
loop through all pivottables in worksheet (use 'on error goto next' in case you have no pivots on that sheet (search on internet for actual code to do this - use the words I typed on this line))
write sheet.name and pivottable.name to an array for each pivot table
I think you can also read the range/location property, and get the range.reference as a string if you need to, but I forget how to do this (Jon's blog should help ith this - or set your pivot table to an object, and brows the properties of the object (this is a valuable trick with pivot tables))

write array to your table of contents sheet
use sheet name to set your hyperlink
use the range string to activate/call a specific location on a sheet

Good luck :thumb