I need macro/vba help: Code to refresh pivot table!!

[lionking2588]

I'm relatively new to vba and require some help constructing a code .

objective: i have a huge set of data 200k+ rows which is updated monthly, each time a new report is ran there is a template sheet where i drop the data and have to manually update three pivot tables linked to it that then do a find remove on one column to get rid of a set list of text. The second stage is to update and refresh the individual pivot tables and then copy this data into a summary.

All I want to do is to be able to put all that into a code so each month i don't have to do the repetitive task of manually removing the same text and refreshing the table etc

Anyone know how?

[TMS]

Suggest you switch on the macro recorder while you do the task(s) manually. That will give you the basic code. It will probably need tweaking and refining to make it generic and more efficient.

At that point, come back with a sample workbook and the embryo code and someone will help you to make it do what you want ... with a little luck

Regards, TMS

[lionking2588]

a common sense move , thanks TMS

[TMS]

You're welcome.

[lionking2588]

Apologies if the below is asking too much as I am a novice to VBA and trying to catch up quickly.
The below is the ‘embryo code’ generated from a recorded macro. Numerous attempts to edit this myself has caused various compile errors so I’m at my wits end.

Please help!!
The objective I want to achieve is to have a button that the user presses once they have entered data into columns l to V the data quantity is upwards of 300k rows each time so filters have been applied from columns A-K.

Once data is entered I want the code to find and replace specific references in column k which removes part of the text. In this example _yes, _no, _maybe. It’s safe to assume these variables will always be at the end of the text. I then want to make sheet two which is the pivot table which will be hidden to appear and refresh .

Then copy and paste special values all that data into a new sheet change ‘Key2’ values to be in a specific order and then copy and paste the data values and key amounts only (without headers) into a set template.
The stages up to this point are quite repetitive hence the need for the coding
There are various pivot tables that will need refreshing and data copied into different sheets relevant to each pivot table so any instructions you can add to the code to help me identify what i can modify would help.

The user will then have instructions of how to manually manipulate the set template only.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Does the attached help. It uses the following procedures

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I also added the dynamic range name "Data" to cover your data set and used that name in the PT. I also added a one line macro in the PT sheet Activate event that automatically refreshes the PT every time you select it. It's also refreshed when you use the first of the procedures above.

[lionking2588]

Thanks Richard Buttrey - this works perfectly