Different approach to create uniques data validation list from table inputted data columns

[jaryszek]

Hi Guys,

i wrote macro which is checking if something new was added/removed/updated to table column and refreshing pivot table in separate tab in order to get unique value.
But it is a lot of code and i do not know - maybe there is another solution which i can use and will be easier and faster?

Can anybody had similar issue?

Please Login or Register  to view this content.

Screenshot_21.png

Code is:
1. checking if something was added/deleted/updated in table
2. if yes looping through table header and checking the match
3. If it is a match - refresh pivot table in hidden worksheet.
4. As result i have a list of uniques from specific column

In attachment example workbook.

All sugesstion are welcome.

Best Wishes,
Jacek

[Mrrrr]

No attachment posted

[jaryszek]

Thanks.

Jacek

[jaryszek]

Hi,

what do yu think Guys? It is a chance to do this better/faster?

Best,
Jacek

[Mrrrr]

Will you have multiple pivot tables containing names existing in a table? I notice that the pivot table name comes from the table in Hidden DV sheet.
Is that needed? Because there is a simple code to refresh the pivot using Vn_Input_Tier_Storage table as source.

I mean you can easily refresh a pivot table in a couple of lines of code.

[jaryszek]

Hi Mrrr,

thank you very much.
How you will know that you added to table column which should update connected pivot table?

I am checking:

Please Login or Register  to view this content.

here if specific column exists in table. If yes - refresh pivot table.
But yes, maybe i could add statement which will refresh pivot table immediately after checking if column exists in dictionary.
It would avoid one additional loop in the end! Thanks!

Hmm i think that it can be done in better way...

Best,
Jacek

[Mrrrr]

In that file will you have pivot tables that are not named from that table?
Or all the pivot tables existing in the document will have a title in there?

Because you could just make a code to refresh all pivots, without checking anything. Just loop through every pivot table and refresh + update it.

Something like:

Please Login or Register  to view this content.

[jaryszek]

Thanks!

Yes but refreshin all pivot is slower than refreshing only choosen ones

Jacek