Pivot Table - Looping Through Pivot Fields

Hey Guy and Dolls,

It’s my first time here, so bear with me if I haven't done something I was suppose to do while posting. Any feedback would be greatly appreciated.

First off, I would like to thank the person/people who created this site so people like me can ask questions. Also I would like to thank the volunteers that use their spare time (or sometimes work time ) to answer my questions. I hope that in the future I will be among these warriors of people.

Ok my question, it’s in regards to pivot table pivot fields.

I have created a macro that will create a pivot table in a specified worksheet and fill in the row and data fields. The problem is I need the data field to include multiple pivot fields that meet a specified criteria.

Is it possible the macro could loop through all available pivot fields, looking for the word 'Points' and then putting the matching pivot field into the data field?

To explain in more detail I have attached a sample workbook with my existing macro and the following worksheets:

1. Data - Contains the data that the pivot table uses. This is generic.
2. Pivot - This is where the macro creates the pivot table. This is generic.
3. CorrectPivot - This shows a pivot table in which I would like the macro to create.

I hope I was as clear as possible.

Kind Regards

Mini12

Hi Mini,

Please find attached.

Regards,
Shekar Goud.

Hey Shekar,

Thanks for answering the question, the only problem is I cannot add the pivot fields individually. This is because I am trying to make this code universal where it can be used on multiple workbooks with many different number of 'Points' fields.

For example, sample1.xls has 2 fields that has 'Points' in the field name, but sample2.xls has 12 fields with 'Points' in the field name.

So to make it universal I need the code to loop through all pivot fields looking for the word 'Points' within the fieldname and then add it to the data field.

I have tried to create this code but failed, this is how far I got:

Please Login or Register  to view this content.

Does anyone have any ideas?

Kind Regards

mini12

Wow that was fast, got a reply before I posted a reply.

Is there mindreaders here?

Thanks, DonkeyOte & shekar goud, you were great help.

I can't believe I was that close to solving the solution myself. Thank you DonkeyOte for filling the missing pieces of the puzzle.

I just then made three minor adjustments, which are as follows:

Please Login or Register  to view this content.

Bingo it works!

Problem solved.

Kind Regards

mini12