Generic VBA Code to update the graphs Automatically. Help please...

[spiwere]

Hi All,

I know this should be an easy one for you…

I have all the data in sheet 1 of an excel from which 4-5 Bar charts used to get updated automatically, but since the time I used the following code to refresh “Pivots” in other tabs..

Please Login or Register  to view this content.

It (auto upadtion of Bar Charts in Sheet 1) has eventually stopped. I’m not sure why, so was thinking if I need another code on sheet 1 along with the above code to get the Bar charts refreshed once the data in the table on the sheet (on which the bar charts are based) is changed due to any changes in the Master Data tab.

I tried recording it, but seems like did something wrong and it did away with the above code as well as the new one did not function accurately.

Could anyone please guide…

[OllieB]

spiwere,

The following "macro" refreshes all pivot tables that exist in your workbook (irrespective of the worksheet). You can trigger the macro manually, via a button or call the routine from the workbook_open event so that the refresh is done automatically every time the workbook is opened.

Please Login or Register  to view this content.

If this works for you do not forget to delete the code shown in post #1 from your project.

[OllieB]

spiwere,

The following "macro" refreshes all pivot tables that exist in your workbook (irrespective of the worksheet). You can trigger the macro manually, via a button or call the routine from the workbook_open event so that the refresh is done automatically every time the workbook is opened.

Please Login or Register  to view this content.

If this works for you do not forget to delete the code shown in post #1 from your project.

[spiwere]

Thanks OllieB.

I think the code for refreshing the "Pivots" I reflected in post 1 is working fine with the Event. However, my bar graphs in Sheet 1 of the same report don't seem to get updated since the time I haved used the code (in Post 1).

Please let me know in case I'm missing anything.

My problem is to do with the Bar Charts not updating.

Please suggest...

[spiwere]

Additionally, in your code to refresh the "Pivots" automatically. You've suggested that I need to call the routine from the workbook_open event so that the refresh is done automatically every time the workbook is opened.

1). Can this be customized by simply the pivots getting updated once the source data is changed.
2). Is it possible to have a VBA Key that can be a part of the report wherein I can add categories related to the "Pivot" items and the respective row can be coloured based on the categories and the required colour specified in the key.

Please advise.

Thanks a lot. You are the best!!!

[OllieB]

The BAR Charts do not have a "refresh" property/method themselves as they are linked to the data in the pivot table. Perhaps your calculation method in the workbook is set to manual, and that is why the charts are not updating? Check the tab FORMULAS on the ribbon, right side "Calculation Options" for the current setting.

Auto updating your pivots based on changes made to the underlying data worksheets can be done by calling the code provided from the worksheet_change event, but keep in mind that this would trigger the routine for every change made, which can slow down your workbook quite dramatically!

question (2), no idea what you are asking

[spiwere]

Got it. The first problem is resolved.

Request 2:

I have Pivots and the categories in the “Pivots” have certain line items that need to be highlighted. I’m requesting a key or something wherein I can change the item name and can get them colored based on the report requirement.

The same colour scheme (I may define) is to be used across all “Pivots” for categories like:

Sold, Follow later, Pipeline, Email and Bad categories in the Pivots.

These need to be highlighted in different colour based on the colour defined in the last Tab of my report named Colour Key.

Somehow, I’m unable to upload a file. It says that the file size is higher than permitted (1.31 MB). Is there another way I can send / attaché it? Please advise.

[OllieB]

spiwere, without an example I fail to understand what you are requesting. Probably due to my limitation to visualise. You can try zipping it.

[spiwere]

I do understand that…Here’s the attached sample report. I’ve reduced the file size by deleting certain tabs and removing the code I mentioned in my first Post. It was used with a Event, Dynamic Named Range.

Please see the colour coding in Pivot 1 “Status By Representative Name” and the requested Key type in Colour Key

Hope this helps me explain it better!

Please let me know if you have any questions.

Thanks a ton...

[OllieB]

So the only thing you need me to do is create a little VBA routine that formats the entries in the pivot table on the Status.... worksheet using the colors/legend shown on the Colour Key worksheet. Please confirm if my understanding is correct

[spiwere]

Yes, your understanding is spot on!

The only point is that this is not the only "Pivot" that needs to be formatted. There will be anything between 5-10 "Pivots"(1 per tab) which will need to be formatted using the colors/legend shown on the Colour Key worksheet.

I would also like to keep the Auto Pivot Refresh code (in my Post 1) or any as you think best which will help me execute this:

1. Refreshing Pivots Data (5-10) each one is in a separate Tab using the Data from the source Data Sheet in my example
2. Colour Coding the items in each of the Pivots based on the colors/legend that I can define / change based on report requirements in the Colour Key worksheet

Do let me know if you have any questions.

As always, thanks a bunch Sir…

[OllieB]

Ok, a combined routine to:
- loop through all worksheets,
- per worksheet loop through all pivot tables,
- per pivot table force a refresh of the data,
- per pivot table loop for all rows in the body,
- per row check if a color code has been assigned using the value shown in column A as a lookup against the legend provided on worksheet Colour Key,
- when a prescribed color is found, apply that color for the entire row of the pivot table taking into account the existing number of columns

Please Login or Register  to view this content.

[spiwere]

AWESOME is a very small word to define this...for sure, for sure....

No matter how much the data is, no matter how much..It works in a blink of an eye...

Just one quick help...I noticed that the key for colours is only seraching the categories in Colum A of the Pivots. Can this be customized to search the categories in any columns within the Pivots.

We are all set...Super excited to close this thread as "Super Solved" courtesy OllieB sir:-)


Best regards,
Spi

[OllieB]

Yes it can be changed.

The logic will then be per row to try to find a color for the text in column 1 first, if not found, it will try to find a color for the text in column 2 etc.

Agreed?

[OllieB]

assuming that is what you want, new code attached

Please Login or Register  to view this content.

[spiwere]

This is almost it. I posted my request prior to you posting the code, but it somehow seems to have vanished. I encountered an error.

The only thing that it needs to do more is that the clouring of the row should start from where the category is found in the pivot. Lets’ say if the category is found in Column 2, then it should start from Column 2 and continue until there is data in the row. In other words in Pivots when the category is not listed in Column 1, it should not highlight / colour Colum 1 but start from Column 2 and so on and so forth...

That is it then

[OllieB]

OK

Please Login or Register  to view this content.

[spiwere]

Respected Sir,

This is Perfect as far as color coding is concerned!

The only thing it isn’t doing is when I add the data in the Data Tab it is not taking it into calculation. On refreshing everything else is getting colored as expected. However, this is not taking into account the new data that is being added. Is this to do something with the Dynamic Named Range etc. I replenish (add / delete) data in the data tab regularly so would want the Pivots to be refreshed dynamically.

Kindly advise…

[OllieB]

spiwere, yes what I have written for you only refreshes the data and applies the colors you want, it had NOTHING to do with expanding the data on the data worksheet. I have check the code and noticed you are using a dynamic named range. I can change this to an actual range based on defined rows in the data worksheet and include setting that in the code if you want? (so basically remove the dynamic named range)

[spiwere]

Yes, I would like to be able to refresh the Pivots data based on any changes in the data tab.

Sorry If I'm missing anything...

Thanks you sir!

[spiwere]

So basically if the dat is added or deleted the Pivots should be refershed accordingly is what I'm seeking your assistance on.

Please let me know If I'm unclear. And yes, I was using dynamic named ranges to use it before.

[OllieB]

Ok.

You can remove named range PivotDNR as it is no longer needed if you use the below code.

Changes to rows and/or columns on the data worksheet will now automatically be applied to all pivot tables when the routine is executed

Please Login or Register  to view this content.

[spiwere]

Stupendous, Amazing, Astounding, Breathtaking, Brilliant, Fabulous, Fantastic, Marvellous

Thanks a lot for this Enormous help! You saved my life man!

I’ll do a comprehensive run of this in the next few days. I believe I can comeback with any questions.

Closing this as solved….

You are the best OllieB!!!!

[OllieB]

Please remember to click the star icon if you are happy with my contribution!

[spiwere]

Dear Sir,

I did click the star. Thanks again for this awesome help! I'll go through it and do a complete run over the next few days.

I'm sure nothing can go wrong now, but I'm assuming I can seek further assistance on this, just in case a need arises.

Thanks a ton!

Best regards,
Spi

[spiwere]

Good morning OllieB!

Hope you are doing great!

I’m currently running your “Master Piece” and its seems to have done wonders. Has very amazingly cut down on my time.

As I was testing, I found out that I’m in need of one small enhancement, which is:

• In My Data Tab Job Level (Column N) is to be mapped in accordance with the Job Title (Column M)
• Column N (Job Level) may or may not be present in the Data Tab
o If it is not available, then I’ll request to be able to added it (Column N) in the in the Data Tab and shift the remaining data to the subsequent columns
o If it is available, then I’ll request only for the mapping to be done. This column is avaialble in some reports and not is others, but is always blank wherever avaialble.

My request is to be able to enhance the last Code so that I may be able to map it with a Key defined in Column M and Column N of the Colour Key Tab. In other words, I need to be able to Map the Job Title to the relevant Job Level as defined in my key. I've attached the a sample report.

The Data in Column N of the Data Tab is then used to create another Pivot in my report. My guess is that this should be the first step in the solution i.e. once I run it. These changes are incorporated, and then I can draw my pivots

Thanks in advance!

Really appreciate your efforts.

Best regards,
Spi

[OllieB]

The following code should do the trick. Note that you job title / job level table on the Colour Key worksheet contains duplicate entries for job titles Sen en Tre so you will need to correct those.

Please Login or Register  to view this content.

[spiwere]

Thanks OllieB.

I'll correct that!

One query, where exactly can I add this snipet to the last code that you shared. So that I can run the enitre bit in one go. Please suggest!.

Regards,
Spi

[OllieB]

Merged version

Please Login or Register  to view this content.

[spiwere]

AWESOME!!!! Thanks a million:-)

You are a ROCK STAR SIR:-)