How to set Filter Criteria in Pivot with Macro?

**cathchin** · 03-06-2012, 11:23 PM

Hi All,

Below are the pivot codes that is in my current macro. However, I only need one filter criteria, as those dates are changing variables everyday that might give me an error in the future, thus, I need to filter those with "TBA" only.. Please help.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"WO_GATED!R1C1:R" & LastRow & "C33", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="TBA!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12

Sheets("TBA").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Con")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA")
.PivotItems("3/6/2012").Visible = False
.PivotItems("3/7/2012").Visible = False
.PivotItems("3/8/2012").Visible = False
.PivotItems("3/9/2012").Visible = False
.PivotItems("3/12/2012").Visible = False
.PivotItems("3/13/2012").Visible = False
.PivotItems("3/14/2012").Visible = False
.PivotItems("3/15/2012").Visible = False
.PivotItems("3/16/2012").Visible = False
.PivotItems("3/17/2012").Visible = False
.PivotItems("3/19/2012").Visible = False
.PivotItems("3/20/2012").Visible = False
.PivotItems("3/21/2012").Visible = False
.PivotItems("3/22/2012").Visible = False
.PivotItems("3/23/2012").Visible = False
.PivotItems("3/26/2012").Visible = False
.PivotItems("3/27/2012").Visible = False
.PivotItems("3/28/2012").Visible = False
.PivotItems("3/29/2012").Visible = False
.PivotItems("3/30/2012").Visible = False
.PivotItems("3/31/2012").Visible = False
.PivotItems("4/2/2012").Visible = False
.PivotItems("4/3/2012").Visible = False
.PivotItems("4/4/2012").Visible = False
.PivotItems("4/5/2012").Visible = False
.PivotItems("4/6/2012").Visible = False
.PivotItems("4/8/2012").Visible = False
.PivotItems("4/9/2012").Visible = False
.PivotItems("4/10/2012").Visible = False
.PivotItems("4/11/2012").Visible = False
.PivotItems("4/12/2012").Visible = False
.PivotItems("4/13/2012").Visible = False
.PivotItems("4/16/2012").Visible = False
.PivotItems("4/17/2012").Visible = False
.PivotItems("4/18/2012").Visible = False
.PivotItems("4/19/2012").Visible = False
.PivotItems("4/20/2012").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA")
.PivotItems("4/22/2012").Visible = False
.PivotItems("4/23/2012").Visible = False
.PivotItems("4/24/2012").Visible = False
.PivotItems("4/25/2012").Visible = False
.PivotItems("4/26/2012").Visible = False
.PivotItems("4/27/2012").Visible = False
.PivotItems("4/28/2012").Visible = False
.PivotItems("4/30/2012").Visible = False
.PivotItems("5/1/2012").Visible = False
.PivotItems("5/2/2012").Visible = False
.PivotItems("5/3/2012").Visible = False
.PivotItems("5/4/2012").Visible = False
.PivotItems("5/5/2012").Visible = False
.PivotItems("5/7/2012").Visible = False
.PivotItems("5/8/2012").Visible = False
.PivotItems("5/9/2012").Visible = False
.PivotItems("5/10/2012").Visible = False
.PivotItems("5/11/2012").Visible = False
.PivotItems("5/14/2012").Visible = False
.PivotItems("5/15/2012").Visible = False
.PivotItems("5/16/2012").Visible = False
.PivotItems("5/17/2012").Visible = False
.PivotItems("5/18/2012").Visible = False
.PivotItems("5/21/2012").Visible = False
.PivotItems("5/22/2012").Visible = False
.PivotItems("5/23/2012").Visible = False
.PivotItems("5/24/2012").Visible = False
.PivotItems("5/25/2012").Visible = False
.PivotItems("5/28/2012").Visible = False
.PivotItems("5/29/2012").Visible = False
.PivotItems("5/30/2012").Visible = False
.PivotItems("5/31/2012").Visible = False
.PivotItems("6/1/2012").Visible = False
.PivotItems("6/4/2012").Visible = False
.PivotItems("6/5/2012").Visible = False
.PivotItems("6/6/2012").Visible = False
.PivotItems("6/7/2012").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA")
.PivotItems("6/8/2012").Visible = False
.PivotItems("6/11/2012").Visible = False
.PivotItems("6/12/2012").Visible = False
.PivotItems("6/13/2012").Visible = False
.PivotItems("6/14/2012").Visible = False
.PivotItems("6/15/2012").Visible = False
.PivotItems("6/16/2012").Visible = False
.PivotItems("6/19/2012").Visible = False
.PivotItems("6/20/2012").Visible = False
.PivotItems("6/22/2012").Visible = False
.PivotItems("6/26/2012").Visible = False
.PivotItems("6/27/2012").Visible = False
.PivotItems("6/28/2012").Visible = False
.PivotItems("6/29/2012").Visible = False
.PivotItems("7/3/2012").Visible = False
.PivotItems("7/6/2012").Visible = False
.PivotItems("7/9/2012").Visible = False
.PivotItems("7/10/2012").Visible = False
.PivotItems("7/11/2012").Visible = False
.PivotItems("7/12/2012").Visible = False
.PivotItems("7/13/2012").Visible = False
.PivotItems("7/17/2012").Visible = False
.PivotItems("7/19/2012").Visible = False
.PivotItems("7/20/2012").Visible = False
.PivotItems("7/24/2012").Visible = False
.PivotItems("7/27/2012").Visible = False
.PivotItems("7/30/2012").Visible = False
.PivotItems("7/31/2012").Visible = False
.PivotItems("8/2/2012").Visible = False
.PivotItems("8/3/2012").Visible = False
.PivotItems("8/7/2012").Visible = False
.PivotItems("8/14/2012").Visible = False
.PivotItems("8/21/2012").Visible = False
.PivotItems("8/27/2012").Visible = False
.PivotItems("8/28/2012").Visible = False
.PivotItems("9/4/2012").Visible = False
.PivotItems("9/11/2012").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA")
.PivotItems("9/18/2012").Visible = False
.PivotItems("9/25/2012").Visible = False
.PivotItems("9/27/2012").Visible = False
.PivotItems("10/2/2012").Visible = False
.PivotItems("10/5/2012").Visible = False
.PivotItems("10/8/2012").Visible = False
.PivotItems("10/9/2012").Visible = False
.PivotItems("10/15/2012").Visible = False
.PivotItems("11/13/2012").Visible = False
.PivotItems("11/20/2012").Visible = False
.PivotItems("12/18/2012").Visible = False
.PivotItems("1/15/2013").Visible = False
.PivotItems("3/13/2013").Visible = False
.PivotItems("4/2/2013").Visible = False
.PivotItems("12/12/2013").Visible = False
.PivotItems("9/1/2014").Visible = False
.PivotItems("9/3/2014").Visible = False
.PivotItems("9/4/2014").Visible = False
.PivotItems("9/8/2014").Visible = False
.PivotItems("12:00:00 AM").Visible = False

**arlu1201** · 03-07-2012, 01:06 AM

Please put your code in code tags as per forum rules. You have put the starting of the tag but missed the [/code] part.

Also, we can have a loop to make the pivotitems visible & invisible, instead of doing it value by value. Why dont you attach a sample file that you are using along with the above code so its easier for us to understand?

**blakec4** · 03-08-2012, 02:36 PM

I am dealing with this same issue. Unfortunetly, I generated the code from recording a maco. To actually filter by hand would take me a couple minutes. The macro that was generated would take 40 minutes.

My specifics:

I have over 400 people I am trying to filter to 18.

**arlu1201** · 03-08-2012, 03:05 PM

Blakec4, its against forum rules to post your question in the thread of another. Even if it relates to the same issue, you need to create your own thread. You can definitely provide a link to the related thread in your thread.

**cathchin** · 03-08-2012, 09:41 PM

Book1.xlsx

Hi Arlette,

As the file size is quite big, I only took out the columns needed with the results needed to be shown as attached. However, yesterday I did try the below. It works, it's just that the Value "TBA" will needed to type manually. Any way/method to use pivot to do it instead of manually fill up the value?

Below is the new code that I have mentioned:

Sheets("TBA").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA")
.Orientation = xlPageField
.Position = 1
End With
Range("B1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA").CurrentPage = "TBA"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Con")
.Orientation = xlRowField
.Position = 1
End With
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "ETA" '<---- manually insert column
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "TBA" '<---- manually insert Value
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.AutoFill Destination:=Range("B2:B" & LastRow)
Range("B2:B" & LastRow).Select