How to Filter a Pivot Table in Excel Using VBA

[@DemetriPanici]

Pivot tables come so in handy at work. Great advice here!

[@MyExcelOnline]

I.LOVE.PIVOT.TABLES! You are right, such a great feature in Excel.Thanks for watching.

[@mbjlukas]

Great and very clear video, your "talking" is very clear and is very detailed, also the visuals you do are 100% perfect , (example the red boxes and highlights) showing on the screen as you talk.

[@MyExcelOnline]

Wow, thank you! I really appreciate you taking the time to write all of that. So glad you like it!

[@rleigh5204]

Fabulous! This tut answers EXACTLY what I was looking for. Well done and thank you. You are my new best friend.

[@MyExcelOnline]

Awesome! You're welcome. So glad you watched!

[@fernandodasilvavelasco8664]

Thank you very much. Save my life at work

[@MyExcelOnline]

You are so welcome! Glad it was helpful.

[@TheRajasekar03]

Macro steps clearly explained

[@MyExcelOnline]

Thanks! Glad you liked it.

[@wayneedmondson1065]

Great! Nice example and code. Thanks for sharing :)) Thumbs up!!

[@MyExcelOnline]

Thank you! VBA is so much fun!

[@chrismiracle]

This is very handy but could you explain how I could filter by a single criteria rather than a date range; eg. a Product Code?

[@MyExcelOnline]

Oh sure! Just change the line similar to this: PivotFilters.Add Type:=xlDateBetween, Value1:=startDate, Value2:=endDate to this: PivotFilters.Add Type:=xlValueEquals, Value1:=whatever value you want the field to equal

[@mohamedmostafa-ds4st]

Very great video I am watching the channel from Egypt I searched a lot for Code to control the filters in the pivot table using vba, but I want code to search for text data other than dates Thank you very much and respect

[@MyExcelOnline]

Hi Mahamed. I am so glad you found us! Thanks for watching and letting us know. Instead of using xlDateBetween (around 13:18), try using xlValueEquals or anything else here: learn.microsoft.com/en-us/office/vba/api/excel.xlpivotfiltertype. Hope that helps!

[@samanthanguyen2927]

Hello, very good and clear video, thank you so much. I just have one quick question, what if i want to copy paste the value to another sheet in the filter area. Exemple, in Sheet 1 it'll be similar to your "Beginner date", so only 1 critieria, i want to then copy paste that value and go to sheet 2 to paste that value in the filter area of the pivot table. How could i do please?

[@MyExcelOnline]

Thanks for the question. Are you asking if the data will automatically update with the pasted data or do you just want to paste the data the way it is? You could record a macro and see what code it writes and customize it from there. If I didn't answer your question in detail enough, we have consulting services that you can contact to help write custom code. Check out www.myexcelonline.com/microsoft-excel-consulting-services/

[@ignacioa3698]

is this workbook that's in this specific video available for download b/c I dont see that option.

[@MyExcelOnline]

Yes, if you look in the description, there is a link to the sample file.

[@dk71arya]

Great! Thanks. How to control pivot table based chart with Alt+Shift+n for next and Alt+Shift+p for previous to change data in chart. Thanks for a reply.

[@MyExcelOnline]

Hi. Could you please let me know which minute n the video you are referring to? Thanks.

[@guhanmuthamizhchelvan8751]

Great tutorial. I have a table where the date is actually in filters and not in rows. Could you please help with a code for the same ?

[@MyExcelOnline]

Hi Guhan. I'm not sure what you mean by in filters? I think the date data has to be in a table format in order to use it in a pivot table.

[@fergusfriel7824]

Great video, the macro only works for me when the Pivot Field is in the Columns or Rows areas, any ideas on how to get it to work in the Filters area?

[@MyExcelOnline]

Thanks for watching! Could you please explain a little more? The VBA code uses the filter on the pivot table, so I am just not sure exactly what you mean.

[@marieganache4099]

@@MyExcelOnline Great video, thank you very much. I have the same problem as Fergus: I want to update the pivot field that is located in the "filters" area of the pivot table, not a pivot field that is located in "Rows" or "columns" area of the pivot table. When the field is placed in "filter Area", it does not seem to work?

[@unknown9000]

@@MyExcelOnline I have the same problem when the dates field is in the filters section of the pivot table (this applies to the sample workbook in the description too) the macros gives error 1004, I found a workaround that is not adding the field to the pivot areas at all, the macro will still filter the field however I will like to know if there is a way to correct the macro to use the field in the filter area?

[@nemypresbiteroph]

I am using pivot table from data model. Could you please help on how to name the pivot fields?

[@MyExcelOnline]

Sure! If you select the Pivot Table, you should see a menu for Pivot Tabe Analyze. On the left of that Ribbon is a button called PivotTable. If you select the down arrow there, you can name the PivotTable whatever you would like. Hope that helps!

[@1029skatingirl]

Can we do just between week number?

[@MyExcelOnline]

I'm sorry, could you please clarify what you mean by between Week Number? There is a filter on the Pivot Table for a number field that is Between. You could record a macro while using it and see what Excel writes!

[@МихаилАкимов-ю6в]

Where can I view the current Table Filter settings? For example: before applying a new filter to a table, I need to remember the current filter settings. How can I view these settings and save their values in Excel cells using VBA?

[@MyExcelOnline]

Thanks for the question. You could try something like this to get the existing values: Dim sht As Worksheet Dim filtarr() As Variant Dim f As Long Set sht = ActiveSheet With sht.AutoFilter ReDim filtarr(1 To .Filters.Count, 1 To 3) For f = 1 To .Filters.Count With .Filters.Item(f) If .On Then filtarr(f, 1) = .Criteria1 Debug.Print .Criteria1 If .Operator Then filtarr(f, 2) = .Operator filtarr(f, 3) = .Criteria2 Debug.Print .Operator & ", " & .Criteria2 End If End If End With Next f End With If that doesn't work, please feel free to contact our consulting services to help you. The link is here: www.myexcelonline.com/microsoft-excel-consulting-services/ Hope that helps!

[@MyExcelOnline]

Thanks for the question! You could try this code to get the existing table filter: Dim sht As Worksheet Dim filtarr() As Variant Dim f As Long Set sht = ActiveSheet With sht.AutoFilter ReDim filtarr(1 To .Filters.Count, 1 To 3) For f = 1 To .Filters.Count With .Filters.Item(f) If .On Then filtarr(f, 1) = .Criteria1 Debug.Print .Criteria1 If .Operator Then filtarr(f, 2) = .Operator filtarr(f, 3) = .Criteria2 Debug.Print .Operator & ", " & .Criteria2 End If End If End With Next f End With If that doesn't help, you could contact our consulting services here: www.myexcelonline.com/microsoft-excel-consulting-services/

[@tadikondaharshamohan5300]

It says invalid date. I'm using the xlspecificdate function? Any idea what could be causing this issue?

[@MyExcelOnline]

Maybe try lxBetween like it is in the sample file and use the same date for begin and end? See if that works first and then some other options. We also have consultants that will look at files for you if you'd like to check out www.myexcelonline.com/microsoft-excel-consulting-services/

[@A_G4]

When I click my macro button, the filter does not work until I go into the pivot table label filter and select OK, any ideas? the values from the cells are already there and it will filter once I select OK, but not when I click my macro button?

[@MyExcelOnline]

Hi Adam. Were you able to go here and download the workbook? www.myexcelonline.com/blog/101-excel-macros-examples/ VBA is pretty picky with the syntax, so every letter has to be similar.

[@robertzheng2069]

what if if you wanna change columns, rather than rows...

[@MyExcelOnline]

It depends on how you set up your Pivot Table. Try moving the fields around to columns and rows and see what happens. The VBA code will be different for columns, however. This list should help: learn.microsoft.com/en-us/office/vba/api/excel.pivottable. You can also contact our consulting services if you have a specific function that you would like help on: www.myexcelonline.com/microsoft-excel-consulting-services/#

[@XxRoos898xX]

This didn’t work for me properly it would filter and not bring up an error but it wouldn’t correctly filter out all the dates outside my date range

[@MyExcelOnline]

Hi Anne. Thanks for watching. Were you able to download the practice file? Could you compare the VBA in the practice file to your file? It is very picky on correct syntax.

[@XxRoos898xX]

​@@MyExcelOnline In the video is the date ordered like this? mm-dd-yyyy? Im from the uk we use dd-mm-yyyy maybe this is causing my issue? when I enter 06/01/2022 (beginning date) and 06/06/2022 (ending date) it actually gives me the data for the 1st of June till the 6th of June. Is there a way around this?

[@ahmed007Jaber]

thank you for this. i am trying to get this but it is not working Sub niiiiiiiiiiice() With ActiveSheet.PivotTables("ByDate").PivotFields("my_special_field") .ClearAllFilters ' Clear any previous filters .PivotFilters.Add Range("B1").Value ' Apply the filter using the value in B1 End With End Sub

[@MyExcelOnline]

Hi. Were you able to download the practice file and see the code? The link to it is in the description on KZbin.

[@ahmed007Jaber]

@@MyExcelOnline hi thanj you for getting back I tried but surprisingly couldnt find the vba module What i am trying to do is copy selected entries from one pivot from one field say entity name and apply it to another table What i am filtering is from fields column name not the filters on top Any idea how would that be possible?

[@MyExcelOnline]

Hi. If you open the sheet and press ALT+F11, you should be able to see the code there. Not sure I can help without seeing your file, but we do have a place you can send them in and ask more technical questions here: www.myexcelonline.com/microsoft-excel-consulting-services/

[@DeveloperChris]

This does not work on office2016 there is no xldatebetween

[@MyExcelOnline]

Yes, we record all of our videos using Excel 365. It may not work on previous versions.

[@_tone_1879]

My spreadsheet is showing an error: Run-time error "1004": This isn't a valid date No matter what I do with the dates I'm just not able to debug this one. Switched the dates to simple "Short date" format e.g. 01/02/2022. No fancy formats not even a dd-mmm-yy format, PasteValue the source dates as well so those are not deriving from a formula. Refreshed the Pivot just in case. Then ran the code and still get the error.

[@MyExcelOnline]

Yes, dates can be a little tricky in pivot tables. Make sure the cells on the pivot table and the column where the dates are coming from are all formatted as the same date format and not text. Also, if you have an invalid date in the original column, that could cause problems as well. Hope that helps...

[@terrencegalang9473]

I have a column that I want to filter a single criteria using combined year and ISOWEEK. here is the format of each cells. =YEAR(A2)&"_Wk"&ISOWEEKNUM(A2) upon, using this in pivot table .PivotFilters.Add Type:=xlValueEquals, Value1:=Range("H2").Value there is run time error "5"

[@MyExcelOnline]

Hi. Are you able to download our sample file and put your data in there and run the code step by step?