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

@MyExcelOnline

2 жыл бұрын

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

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!

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

2 жыл бұрын

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

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

@MyExcelOnline

2 жыл бұрын

Thank you! VBA is so much fun!

Thank you very much. Save my life at work

@MyExcelOnline

2 жыл бұрын

You are so welcome! Glad it was helpful.

Macro steps clearly explained

@MyExcelOnline

6 ай бұрын

Thanks! Glad you liked it.

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!

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

2 жыл бұрын

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

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/

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

2 жыл бұрын

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

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.

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

2 жыл бұрын

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?

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.

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!

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

2 жыл бұрын

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.

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/

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!

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

3 ай бұрын

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

3 ай бұрын

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/

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

@MyExcelOnline

11 ай бұрын

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/#

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

2 жыл бұрын

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

2 жыл бұрын

​@@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?

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

2 жыл бұрын

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...

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?