how to autofilter a pivot table?

[fluffyvampirekitten]

Basically , I have created a pivot table in a new worksheet .
But I dont have any idea to create a data filter of the pivot table in vba code ?

I want to filter all data with the value(Count of Last Name) = 1

Based on macro recorder for fitler data:

ActiveSheet.Range("$A$3:$C$122").AutoFilter Field:=2, Criteria1:="1"

Here's my code for Create Pivot Table:

Private Sub CreatePivot()

Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim objField As PivotField
Dim rngData As Range
Dim wsData As Worksheet 'Old ws
Dim dataField As PivotField
Dim lastRow As Long
Dim lastColumn As Long

'Select the sheet and first cell of the table that contains the data.
'Set worksheet which contain the source data
Set wsData = Worksheets("Simpat")

'Delete any prior pivot tables
For Each pt In wsData.PivotTables
    pt.TableRange2.Clear
Next pt
    
'determine source data range (dynamic):
'last row in column no. 1:
lastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
'last column in row no. 1:
lastColumn = wsData.Cells(1, Columns.Count).End(xlToLeft).Column

Set rngData = wsData.Cells(1, 1).Resize(lastRow, lastColumn)

'Create Pivot Caches
 Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:= _
        xlPivotTableVersion15)

'Creating Pivot Table
Sheets("SimPat").Select
Set pt = pc.CreatePivotTable(TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15)

'Turn off automatic updation of Pivot Table during the process of its creation to speed up code.
pt.ManualUpdate = True

'Setting fields
Set objField = pt.PivotFields("Last Name")
    objField.Orientation = xlRowField
    objField.Position = 1
    
'Set Data Field
  With pt.PivotFields("Last Name")
  .Orientation = xlDataField
  .Function = xlCount
  .Position = 1
  End With
          
  With ws
  .AutoFilterMode = False
  .Range("A1").AutoFilter
  .Range("A1").AutoFilter Field:=2, Criteria1:="1"
  ' .Range(wsData.Cells(1, 1).Resize(lastRow, lastColumn)).AutoFilter Field:=2, Criteria1:="1"
  End With
  
'Turn on automatic update / calculation in the Pivot Table
pt.ManualUpdate = False

End Sub

Attachment 407580

This what will happens , after filtering the data to be "1"
Then , I would like to make the "Row Labels" or "Name" to be same
regardless if the name has spacing or capital letters. Just want to make it the same. How do i do this too ?

Please help

[humdingaling]

attach a worksheet
cannot work with an image

[fluffyvampirekitten]

attach a worksheet
cannot work with an image

What do you mean ?

[humdingaling]

attachment is a jpg...cannot work with jpg

[fluffyvampirekitten]

I attached the sample workbook in the previous post

[humdingaling]

you cant autofilter a pivot table

replace

  With ws
  .AutoFilterMode = False
  .Range("A1").AutoFilter
  .Range("A1").AutoFilter Field:=2, Criteria1:="1"
  ' .Range(wsData.Cells(1, 1).Resize(lastRow, lastColumn)).AutoFilter Field:=2, Criteria1:="1"
  End With

with

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Last Name").PivotFilters. _
        Add Type:=xlValueEquals, dataField:=ActiveSheet.PivotTables("PivotTable1"). _
        PivotFields("Count of Last Name"), Value1:=1

[fluffyvampirekitten]

you cant autofilter a pivot table

replace

  With ws
  .AutoFilterMode = False
  .Range("A1").AutoFilter
  .Range("A1").AutoFilter Field:=2, Criteria1:="1"
  ' .Range(wsData.Cells(1, 1).Resize(lastRow, lastColumn)).AutoFilter Field:=2, Criteria1:="1"
  End With

with

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Last Name").PivotFilters. _
        Add Type:=xlValueEquals, dataField:=ActiveSheet.PivotTables("PivotTable1"). _
        PivotFields("Count of Last Name"), Value1:=1

OMG! It can work. Thank You T^T