Thread: VBA Pivot selecting only data

John and 0EGO,

Apologies for the belated response, I was a little occupied in implementing your suggestions and neatening my macro. I can safely say that the formula does work!- Many thanks to both of you, it has been causing me a pain for a while.

Just to add to the problem, I do have a couple of other issues I was hoping the two of you (and any other who read this) would help me resolve.
Firstly: having created the pivot tables, I was hoping to rearrange them in descending order. However the issue I am having is the code isn't working. There are, as far as I know, two codes I can try;

Sub SortDec()
'
' Macro1 Macro
' Macro recorded 31/01/2012 by 533532297
'
ActiveSheet.PivotTables(1).PivotField("names") _
.AutoSort xlDescending, "no. hands"
'
  
End Sub

The code is giving me an error. The other code is one where I just recorded a macro to establish what the code would be, the issue with this is that it is for a fixed range, therefore it won't cater to a change in the data.

               Range("E5").Select
Range(Selection, Selection.End(xlDown)).Select
   Selection.Sort key1:="R5C5", Order1:=xlDescending, Type:=xlSortValues, _
        OrderCustom:=1, Orientation:=xlTopToBottom

I have attached a dummy workbook which attempts the former code. Please Advise.

The second issues I have is as follows;

I have a Pivot Table, within the pivot item list ("Lowest Ratings") I have options which range from 0 - 21.
I would like to create a code whereby the instructions are; to deselect any items from the pivot item list which are less than 11.

Please Advise!



Many Thanks,

A.

For your second problem, see if this code helps you get started. It's in the attached workbook as well.

Sub Jomili()
Dim PT As PivotTable
Dim pvtItem As PivotItem

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    
    Set PT = ActiveSheet.PivotTables("PivotTable1")
    PT.AddFields RowFields:="Region"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
        .Orientation = xlDataField
        .Caption = "Sum of Value"
        .Function = xlSum
    End With
    
    'Change the pivot table to only give us the info we need
                    For Each pvtItem In PT.PivotFields("Region").PivotItems
                If pvtItem.Value < 11 Then
                    pvtItem.Visible = False
                Else
                    pvtItem.Visible = True
                End If
            Next
        
End Sub

Hi Rory,

Thanks for your response, I have tried running the code with the suggested correction, I am still receiving error messages.

I have attached the workbook with the correction.

Please advise.



Thanks,

Axl

Hi Jomilli,

Thanks for your response, the code works well. The only issue I have is that I have various pivot tables on the same worksheet. Not all of them have the pivotfields("Region"). therefore I would like the code to specidically operate on the pivottable("AllPivot4").

I made various attemps to do this, but all were unsuccessfull.

Please advise.

Thanks,

Axl

Hi AMXl,

You said

I have various pivot tables on the same worksheet. Not all of them have the pivotfields("Region"). therefore I would like the code to specidically operate on the pivottable("AllPivot4").

I think you mean "in addition to the PT we just created", right? If so, to work on different PivotTables you just need to define them. See below for untested sample code:

Sub Jomili()
Dim PT As PivotTable, PT2 as PivotTable
Dim pvtItem As PivotItem

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    
    Set PT = ActiveSheet.PivotTables("PivotTable1")
    PT.AddFields RowFields:="Region"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
        .Orientation = xlDataField
        .Caption = "Sum of Value"
        .Function = xlSum
    End With
    
    'Change the pivot table to only give us the info we need
                    For Each pvtItem In PT.PivotFields("Region").PivotItems
                If pvtItem.Value < 11 Then
                    pvtItem.Visible = False
                Else
                    pvtItem.Visible = True
                End If
            Next

   'Work on another pivot table on the same sheet
    Set PT2 = ActiveSheet.PivotTables("AllPivot4")
    PT.AddFields RowFields:="Whatever"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
        .Orientation = xlDataField
        .Caption = "Sum of Value"
        .Function = xlSum
    End With
    
    'Change the pivot table to only give us the info we need
                    For Each pvtItem In PT.PivotFields("Whatever").PivotItems
                If pvtItem.Value < 11 Then
                    pvtItem.Visible = False
                Else
                    pvtItem.Visible = True
                End If
            Next
                
End Sub.

Hi Jomilli,

I have attached a dummy sheet, the headings for the sheet is exactly as the ones I am using for my actual workbook, but the data is irrelevant.
In the workbook you will find that when you run the macro, there are four pivots being created. I want to use your code "'Change the pivot table to only give us the info we need" specifically for the last two pivots, however when I insert the code it doesn't do that.

Therefore I was wondering if, when inputting the code, we could specify which pivot table to apply it to.



Many Thanks,

Axl