Pivot Table - Looping Through Pivot Fields

**mini12** · 10-07-2009, 06:03 AM

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:

Data - Contains the data that the pivot table uses. This is generic.
Pivot - This is where the macro creates the pivot table. This is generic.
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

**shekar goud** · 10-07-2009, 06:33 AM

Hi Mini,

Please find attached.

Regards,
Shekar Goud.

**DonkeyOte** · 10-07-2009, 07:17 AM

Mini12,

I think in fact you want something a little more dynamic - ie code along the lines of:

Code:

Public Sub MakePivotTable()
Dim pt As PivotTable, ptField As PivotField
Dim WSD As Worksheet, PTOutput As Worksheet
Dim PTCache As PivotCache
Dim PRange As Range
Dim finalRow As Long, finalCol As Long
Set WSD = Worksheets("Data")
Set PTOutput = Worksheets("Pivot")
With WSD
    Set PRange = .Range(.Cells(1, "A"), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column))
End With
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set pt = PTCache.CreatePivotTable(TableDestination:=Sheets.Add.Cells(3, 1), TableName:="SamplePivot")
With pt
    .ManualUpdate = True
    .AddFields RowFields:=Array("ID")
    For Each ptField In .PivotFields
        If InStr(UCase(ptField.Name), " POINTS") Then
            .AddDataField ptField, , xlSum
        End If
    Next ptField
    .ManualUpdate = False
End With
End Sub

The above will create the PT and iterate all fields adding any containing " Points" to the Data Field section.

Note in the above for testing I altered the destination of the PT to be a new sheet created at run-time (change as necessary).

**mini12** · 10-07-2009, 07:18 AM

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:

Code:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

For Each pf In pt.PivotFields
    
    If InStr(pf.Value, "Points") = 1 Then
    
        'Do Something

    Else

        Exit Sub
        
    End If

Next pf

Does anyone have any ideas?

Kind Regards

mini12

**mini12** · 10-07-2009, 07:19 AM

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

Is there mindreaders here?

**mini12** · 10-07-2009, 07:27 AM

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:

Code:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

For Each pf In pt.PivotFields

    If InStr(UCase(pf.Name), "POINTS") Then

        pt.AddDataField pf, , xlSum

    End If

Next pf

Bingo it works!

Problem solved.

Kind Regards

mini12

**romperstomper** · 10-07-2009, 09:24 AM

Just FYI, there is no need to use a separate function to convert the name to upper case, since InStr has an argument to specify whether the comparison should be case sensitive:

Code:

If InStr(1, pf.Name, "POINTS", vbTextCompare) Then

FWIW.

**DonkeyOte** · 10-07-2009, 10:49 AM

Thanks R.... takes me another step further from a smarta$$ and one closer to a dumba$$

Thread: Pivot Table - Looping Through Pivot Fields

Thread Tools

Display

Pivot Table - Looping Through Pivot Fields

Re: Pivot Table - Looping Through Pivot Fields

Re: Pivot Table - Looping Through Pivot Fields

Re: Pivot Table - Looping Through Pivot Fields

Re: Pivot Table - Looping Through Pivot Fields

Re: Pivot Table - Looping Through Pivot Fields

Re: Pivot Table - Looping Through Pivot Fields

Re: Pivot Table - Looping Through Pivot Fields

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions