Excel VBA Introduction Part 51.1 - Introduction to Pivot Tables in VBA

[@vijayfulwar6172]

You really did the great job creating this tutorial. Many of us got benefited and learned from the tutorial. Thanks a lot and guys there are around 40000 views and only 298 likes, we cannot be this much selfish... At least we can express thanks by liking the videos.

[@simplejuan]

An excellent Tutorial on Excel VBA PivotTables. A huge applause to WiseOwl. Thank you very much from the bottom of my heart!

[@WiseOwlTutorials]

ありがとうございます！

[@jmathew6988]

A STRONG FOUNDATION IS ESSENTIAL FOR A STRONG EDIFICE! Really I feel indebted to Andrew Gould each time I refer WiseOwl VBA tutorials. They invariably are my reference manuals in times of need. But each time I clear a doubt I also end up broadening my skills because of the in-depth detail and extensive coverage of topics. The long length of each tutorial indicates the sincerity and great efforts put into detailing for the sake of the viewer. And all for free. Acknowledged and Appreciated for all your efforts. Thank you Andrew sir!

[@WiseOwlTutorials]

Thanks so much for writing such a nice comment - it's great to hear that you've found the videos so useful. Thank you for watching!

[@arunkumark6351]

I spent almost 12 hours searching for good resource to learn how to create picot in newsheet. Within 8 minutes here, I got it cleared and the code is working great. Awesome Content. Thank you so much.

[@WiseOwlTutorials]

Happy to hear that Arun! Glad that you found the video useful, thanks for taking the time to leave a comment!

[@jerzykepinski]

this is fantastic!

[@WiseOwlTutorials]

Happy to hear you enjoyed it, thanks for watching!

[@niczoom]

Great video, I learnt more watching this video than reading through many google search results on pivot tables.

[@abhijitmaitra2200]

I am deeply, profoundly grateful to you. You have no idea how badly I needed this one, my wish has been granted. You are the GOD of vba..............thank you thank you thank you and thank you.

[@rks42188]

I want to hug 🤗 you and make your portraits. Atlleast for VBA you are my inspiration. I can’t praise you in words. Lot of love from India 🇮🇳

[@kl8801]

im so glad the guy like you exist on this world. KEEP UP THE GOOD WORK. YOU ARE THE BEST.

[@PrinceGupta-fl5kv]

THANK YOU SO MUCH !!! your channel provide me the best I were searching for👍👍👍

[@WiseOwlTutorials]

You're very welcome, happy to hear that you found it useful!

[@MuhammadAli-sq9gz]

Andrew Gould is pure Gold. Thank you for all the effort you put into these videos.

[@corporatecentral67]

Thanks a ton! I have been watching your videos since last many years and I owe to you every single line of VBA code that has worked wonders for me. What I could not get of many paid video tutorials about VBA is covered at length in this comprehensive tutorial series. No word in English dictionary is enough for me to express my gratitude. The video series has become a lifetime reference for me to come back every time after a while and refresh my knowledge about VBA. Heartfelt Thanks!!

[@ganeshtanpure9475]

Fantastic training given by you on "Pivots" through VBA indeed! Thank you so much...

[@WiseOwlTutorials]

You're welcome, Ganesh! Thank you for watching!

[@adamlanghans]

I was literally just about to start automating a big pivot table report at work tomorrow, you have set me up very nicely - THANK YOU!

[@anthonyschilling7132]

Thank you so much for putting these tutorials on youtube. You are an amazing teacher. I've learned so much already!

[@joannamucha7635]

This is soooo helpful! Thank you! Just created my first serious macro that really work great!

[@WiseOwlTutorials]

You're welcome Joanna, happy to hear that it helped and thanks for watching!

[@siten1]

You're a God among men, thank you!

[@WiseOwlTutorials]

😀thanks!

[@funniq]

Hahaha ... 31:23 LOL ... you gotta love Andrew

[@sim7717]

OMG!!! You just left me dumbfounded!! Thanks a zillion. Waiting for the upcoming one if there is any.

[@sim7717]

The reason behind my excitement is because, for the last two years i have been roving around different sites looking for complete tutorials on VBA but found not a single one containing so elaborately detailed lesson on chart, pivot table etc. Moreover, you covered how to scrape the web data in such a manner and with so many materials that it is still hard for me to believe that VBA has so many properties to work with especially in the field of grabbing web data like java, python ,ruby etc.Thanks again.

[@frodounterberg1113]

Thank you so much - tried so hard to fix a problem with pivot Tables I had - found solution (as always) in your videos :D

[@WiseOwlTutorials]

Happy to hear it, thanks for watching!

[@ronykrell]

Hi, Great Video. One suggestion - mention that if there's a pivot table field you want to show up both a normal (e.g. row/column) field and a datafield, you need to declare it as a DataField first for it to show up in both places. Otherwise it won't show up as a row/column field and only as a DataField

[@ameleyza7742]

Funtastic tutorial... Thank you so much

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@without-u5965]

I'm so sad now, because i saw your channel just now not before - thanks you

[@melvin9993]

Perfect demonstration

[@Sameerkhan-cs8ln]

Thanks Andrew for another great series. Looking forward VBA SharePoint automation.

[@pradeeprawatvlogs8358]

Sir Thank you So much I don't have words for you, You're awesome sir, Love form India

[@WiseOwlTutorials]

Thank you, Pradeep!

[@sogero2]

Oh my gosh! This is going to make a project I'm working on so much less tedious! I have a large number of workbooks for different entities that I want to perform the same pivot functions on, so if I combine this with some looping, I can get the computer to do all the repetitive work for me. This will give me more time for analysis that humans are valuable for instead of mindlessly repeating the same tasks via GUI.

[@abhilashasharma1629]

The best Tutorial and Tutor.

[@jeffdavis7150]

This really helps simplify the complexity of Pivot Tables in VBA code! Why is it that the pt.AddFields and pt.AddDataFields methods do not require parenthesis around their arguments? Actually, it seems that these methods fail when you even try to use parenthesis. Thank you!

[@WiseOwlTutorials]

Thanks Jeff! You might find this video useful kzbin.info/www/bejne/jH7TgXd8gaZ8nLc It doesn't cover pivot tables explicitly but explains exactly when and when not to use parenthesis in VBA I hope it helps!

[@jeffdavis7150]

@@WiseOwlTutorials Brilliant; that explains it. Thank you for the thoughtful and speedy reply!

[@WiseOwlTutorials]

@@jeffdavis7150 no problem Jeff, happy it helped!

[@tajsay]

thanks Andrew for the videos

[@amandafernandesramospasson5862]

great tutorial, thank you so much!!!!

[@WiseOwlTutorials]

You're very welcome, Amanda. Happy to hear you're enjoying the videos!

[@etorebellino]

Andrew, you are great!

[@manndipp]

Thanks a ton for your efforts

[@kartickchakraborty9135]

Sir, I'm one of your subscribers for years. Sir, would you kindly make a video on "How to split number and text from an Alphanumeric String through VBA UDF?"

[@WiseOwlTutorials]

Hi Kartick! I'll add it to the list, thanks for the suggestion!

[@kartickchakraborty9135]

@@WiseOwlTutorials Thank you Sir. I know it can be easily down with Power Query. But to learn VBA more, I want you to make a video on this topic through VBA.

[@krn14242]

Thanks Andrew.

[@RvP6384]

YESSSSS!!!! THANK YOU ANDREW!!!!

[@hadireg]

I feel sad for non-english speakers! they're missing a treasure around your channel!!

[@rebeccachan7031]

Thank you so much! It's helpful to my work!

[@manishpanwar1421]

Thank you sir!

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@hericklenin]

This is awesome. Thank you!

[@Kurt1968]

Hi Andrew. Greetings from the States! Another superb video - I'm a big fan of the WiseOwl channel and I've watched (and rewatched) almost all of the 102 intro videos....Would you consider doing a video on Tables/ListObjects in VBA? Or maybe you already touch on it somewhere else?

[@WiseOwlTutorials]

Hi Kurt, great to have you with us! I was certain that I'd covered ListObjects previously but I can find no record of it in my video list so I must be imagining it. It's definitely an oversight and I've added this topic to the list although it may take some time for this to materialise, sorry, materialize! In the meantime, thanks for the comment and for watching!

[@Kurt1968]

@@WiseOwlTutorials No worries! Glad I was able to point it out. I'm sure it would make a great addition to the already amazing Excel VBA knowledgebase you've created. Looking forward to seeing it sometime in the future.

[@Mitology]

Hello, great video! I'm struggling to find a way to loop through a pivot field but just with the current filters applied to that pivot table. What I end up with is the loop going through all individual transactions in the pivot cache. I can't find anything on Google that'd help me. I would appreciate if you could direct me to one of your videos that would help me. Thank you very much

[@adriandorland4074]

Hi and thank you for you tutorials they are very helpful. I have some code that works on 2010 and Microsoft Office 365. The code is as follows: ' Create a sheet for a new pivot table Dim PSheet As Worksheet ' To define source data range Dim PRange As Range ' To get the last row Dim lastRow As Long ' To get the last column Dim LastCol As Long ' To use as a name for pivot table cache Dim PCache As PivotCache ' To use as a name for our pivot table Dim PTable As PivotTable On Error Resume Next Application.DisplayAlerts = False ' If a worksheet already exists for the new Pivot Table delete it Worksheets("FOI Totals").Delete ' Then create a new worksheet for the Pivot Table Sheets.Add After:=wsData ActiveSheet.Name = "FOI Totals" Application.DisplayAlerts = True ' Assign the new Worksheet to PSheet Set PSheet = Worksheets("FOI Totals") ' Dynamically work out the SourceData range to be used for the new Pivot Table lastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row LastCol = wsData.Cells(1, Columns.Count).End(xlToLeft).Column ' Assign the new SourceData range to the PRange variable Set PRange = wsData.Cells(1, 1).Resize(lastRow, LastCol) ' Setup the Pivot Table Cache and use the dynamic PRange variable to set the Range of cells for the SourceData Set PCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange) Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="FOITotalsPivotTable") With ActiveSheet.PivotTables("FOITotalsPivotTable").PivotFields("Reporting month") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("FOITotalsPivotTable").PivotFields("Division") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("FOITotalsPivotTable").PivotFields("Department") .Orientation = xlRowField .Position = 3 End With With ActiveSheet.PivotTables("FOITotalsPivotTable").PivotFields("FOI Request Breached") .Orientation = xlColumnField .Position = 1 End With Range("B4").Select ActiveSheet.PivotTables("FOITotalsPivotTable").PivotFields("FOI Request Breached"). _ PivotItems("No").Caption = "Not Breached" Range("C4").Select ActiveSheet.PivotTables("FOITotalsPivotTable").PivotFields("FOI Request Breached"). _ PivotItems("Yes").Caption = "Breached" Range("C5").Select Columns("C:C").EntireColumn.AutoFit ActiveSheet.PivotTables("FOITotalsPivotTable").AddDataField ActiveSheet.PivotTables( _ "FOITotalsPivotTable").PivotFields("FOI Request Number"), "Number of Requests" _ , xlCount Range("B2").Select ActiveSheet.PivotTables("FOITotalsPivotTable").TableStyle2 = "PivotStyleLight14" ActiveSheet.PivotTables("FOITotalsPivotTable").TableStyle2 = "PivotStyleMedium12" End Sub The issue is: The original code I used this on had 27 Columns on Excel 2010 and it works but since it went up to 60 columns it does not work on 2010. But this code works on Microsoft 365 even with 60 columns!!! Ps. I did try PCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsData.Name & "!" wsData.Range("A1").CurrentRegion.Address) but this did not work but works with the PRange Variable. Any suggestions to why this does not work on 2010? Thank you in advance, Adrian

[@RachelMo-d8d]

Amazing videos!! Are there any on tables? Trying to copy multiple PivotTables to a Table(list object)

[@WiseOwlTutorials]

Hi! Sorry, no videos on working with ListObjects in VBA at the moment - the topic is on the to do list!

[@chandraprakashr3132]

First of all I would like to thank you for giving wonderful knowledge. Question: Can I pass an array into the source data as I was thinking instead of giving the excel sheet range why can't I directly pass the array into it. Please suggest with your answer. Thanks Again for the wonderful work. I m using below code and getting Invalid procedure call or Argument error: Dim arr As Variant arr = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion Dim pvt As PivotCache Dim tble As PivotTable Set pvt = ThisWorkbook.PivotCaches.Create(xlConsolidation, arr) Set tble = pvt.CreatePivotTable(Sheets("Sheet3").Range("A3").Select)

[@WiseOwlTutorials]

Hi Chandra, thank you for the kind words! Unfortunately, an array isn't a valid option for the SourceData parameter of this method. If you're using xlConsolidation as the SourceType, the only valid option is an Excel Range object docs.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create

[@shorn77777]

You're excellent

[@shorn77777]

hi, do you have any online course. i had attended a few of them. but coming to UK would be tough. Sir

[@donbogdala5428]

Excellent!

[@luismorana5072]

Hello Andrew, I saw your book in Lulu and would like to know if you have included exercises for the reader to practise what he/she had learnt. I think this book should be a wonderful complement to the excellent tutorials you have in KZbin. Let me know your thoughts. Thanks.

[@UzmanExcel]

Best best besttttt at KZbin

[@michelvanvliet6103]

Once the following section is run from my personal.xlsb always get an run-time error "5" Invalid procedure call or argument... Set pt = pc.CreatePivotTable( _ TableDestination:=ActiveCell, _ TableName:="abc") Any ideas how I can resolve this?! :-( Been looking for hours but no idea...

[@Robleh100]

Yes, Andrew has certainly taught legions of viewers vba across all MS apps. My latest request is for my job too. I am at the stage of wondering if it's possible. It's an excel module that creates a pivot of few columns with totals. However sometimes a column has blank rows. My boss would like to hide the blank rows. I know I can hide an entire column and have played with the module to do that. But this isn't right it will hide the total row too. I want to hide cells containing 'blank'. This would require an IF statement I suspect. I know I'm being vague here. I paste in some code with necessary elements redacted for obvious reasons. I just wondering if it's possible first.

[@WiseOwlTutorials]

Hi there, I can't quite picture what it is you're trying to achieve, is this the property you're looking for docs.microsoft.com/en-us/office/vba/api/excel.pivottable.nullstring This lets you control the value displayed in blank cells in the pivot table. I hope it helps!

[@Robleh100]

@@WiseOwlTutorials That is in the nature of what I want. This will return NA value. I want something like IF NA THEN hide cell. I don't see that as property in a method. I want an if/then routine that hides cells based on the string 'blank' in the cell from the pivot filter. I think that's clearer in lay terms not vba of course.

[@WiseOwlTutorials]

Hi there, you can use the same property to set the cells to an empty string so that they appear blank - the NA string was simply used as an example.

[@Robleh100]

@@WiseOwlTutorials understood. but I want to hide the empty cells also. Specifically, hide the cells if every one is blank. But not hide the grand total. This is why I can't use hide entire row value.

[@Robleh100]

@@WiseOwlTutorials let's leave this one aside for the moment. A much more important issue you covered in one of your videos has to do with sending xcel email attachments. I've looked at the object browser attachments and find it has application as a class but not the property getopenfilename. This is strange. I would think I could insert a file to an attachment with this method and be able to specify a pathway. In your video you show how to specify an attachment object like a jpg, but it would seems that the above description what is really needed as a user interface. Comments appreciated. You don't have to show me how, just confirm it's possible. I'll research the method. The only way to really learn is thru that.

[@TitoLatino]

Andrew, first of all the tutorials are brilliant in both example and explanation. May I make a tutorial request? If one double clicks on the 3 count of Martial Arts at 37:31, thinking one can look up the three choices, all 10 counts will show up in a separate new sheet. Is there a way to isolate or create a dynamic table based on what's filtered and sliced instead of all Martial Arts movies? Thank you so much, it's been very educational!

[@TitoLatino]

Ah! I should have included which version I was using. I am using Excel 2013. So, I’m bugged. I hope the suggestion works. I’ll have to include a few page filters since I have more than one slicer and connect the slicers and page filters accordingly.

[@dasrotrad]

Thank you for the video. I can get a pivot table to work with one Datafield. I cannot configure a Pivot Table with multiple data fields. Am I missing something? How do you add multiple DataFields?

[@surendradig2624]

Is there any latest update syntax for pivot table in 2020 or it is supposed to be this hard ,I mean rest of topics looks kinda easy compared to this ,and this is old wiseowl video , new 2020 wiseowl videos have no pivot table topic .

[@MyYoni2011]

Amazing!!

[@robertguzman491]

Thank You!

[@PachAvin]

Clarification Required: I try to create pivot table from a named table. If I use SourceData as 'Range.CurrentRegion', it gives me the error message starting with "PivotTable field name is not valid". But if I set the SourceData to the name of the table in double quotes, it works. What would be the reason?

[@jorgeruiz3420]

First: Thank you!! This is great, I have a question I wrote a macro to create a new excel file adding data to it and creating a pívot table but when I run the macro from the original file I get an error: run time error 5: Invalid procedure call or argument and it points to the part when create the pívot table and destination argument… please help

[@WiseOwlTutorials]

Hi Jorge! If you have multiple workbooks open make sure that your code is referring to the workbook that you think it is. When you refer to worksheets and ranges without qualifying the reference, Excel will assume that you're referring to the workbook that is currently active. I hope that points you in the right direction!

[@yashsomaiya8141]

Hi I have one question why didn't you make even a single video MS Access macro?

[@lokeshbatra3500]

Hi, i am using the same coding but i have stuck in first line In pivot cache command, current region property is not showing in vba window

[@MrStealthangel]

Hello, thank you much for all your tutorials thus far, I am able to learn so much about VBA! I am trying to create a subroutine that can be reused to create pivottables and filters for different sets of source data. There is a specific pivot item which i would like to remove, but might not be present in the source data. Is there a way to write a subroutine that removes a pivot item IF the pivot item is present?

[@MrStealthangel]

Thank you so much once again! :)

[@murdoc_456]

Hello there,thank u for your video they were very usefull,thank u again,i just have one question,wich range i have to use for including every data that i want to extract a pivot table,the problem is that i don't wnat to change the range every time that i insert a new row or column...

[@murdoc_456]

That helps a lot,thank u

[@yaniv9261]

Hi, Andrew, Great work! Can you please advise how to write a code that creats the pivot table on another workbook?

[@andrewwest5344]

HELP. I want to select only specific items, how do I remove all the currently selected items before selecting the new items that I want. I dont think your video explained that? Thanks!

[@WiseOwlTutorials]

Hi Andrew! There's the part of the video which shows the ClearAllFilters method if that's what you mean? learn.microsoft.com/en-us/office/vba/api/excel.pivottable.clearallfilters

[@andrewwest5344]

Thanks again, you helped me once before too, your videos and method of teaching are second to none!

[@pearsonyam8676]

I"m getting an error when we set the pivot table = pivot cache. set pt = pc.CreatePivotTable( _ TableDestination:="ActiveCell, _ TableName:="Table1") I'm also getting an error in the second method of creating a PivotTable where we set the PivotTable variable. This part is highlighted and I get a run time error 1004 saying: "The PivotTable field name isi not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field." I've tried different table names, but I'm still stuck on the same area. What am I doing incorrectly? Set pt = ws.PivotTables.Add( _ PivotCache:=pc, _ tabledestination:=ActiveCell, _ TableName:="table1")

[@juanpablohorn6642]

Het Andrew! I'm having a very peculiar problem while attempting to create a pivot table. I set my SourceData when I use the method create on my pivot cache, but when I insert a new sheet the pivot cache's Source Data changes to refer to the sheet I just inserted. Why would you happen to know why this happens? For example, my source data is set to "Top Measures'!R1C1:R2667C26", but when I insert a new sheet with Sheets.add, the Source Data changes to "Sheet28'!R1C1:R2667C26". I noticed that this does not happen on the code we used for this first example.

[@WiseOwlTutorials]

Hi Juan Pablo, that does sound unusual. Can you post your full subroutine code?

[@juanpablohorn6642]

@@WiseOwlTutorials Here it goes! Dim pc As PivotCache Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet Dim SourceData As String Application.ScreenUpdating = False ThisWorkbook.Activate ' Other Sub Routines--------- DeleteAllButTMSheet WeeksInOD CountIL1AndIL2 ------------------------------ Sheets.Add After:=TM SourceData = TM.Name & "!" & TM.Range("Ini").CurrentRegion.Address If ThisWorkbook.PivotCaches.Count = 0 Then Set pc = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Chr(34) & SourceData & Chr(34), _ Version:=xlPivotTableVersion15) Else Set pc = ThisWorkbook.PivotCaches(1) End If Set ws = ActiveSheet ws.Select ws.Name = "PivotTable" Range("A3").Select ' Create Pivot Table Problem is in this line---->' Set pt = pc.CreatePivotTable( _ ' TableDestination:="PivotTable!R4C2", _ ' TableName:="TopMeasures", _ ' DefaultVersion:=6) ' Add Table Rows Set pf = pt.PivotFields("Project #") pf.Orientation = xlRowField Set pf = pt.PivotFields("Project Name") pf.Orientation = xlRowField ' Add Table Values Set pf = pt.PivotFields("Weeks in OD") pf.Orientation = xlDataField Set pf = pt.PivotFields("IL") pf.Orientation = xlDataField pf.Function = xlCount pf.Caption = "Number of IL" ' Add Table Filters Set pf = pt.PivotFields("PC") pf.Orientation = xlPageField Set pf = pt.PivotFields("Subproject") pf.Orientation = xlPageField Set pf = pt.PivotFields("IL") pf.Orientation = xlPageField ' Add Todays Date ws.Range("E1").Value = "Date" ws.Range("F1").FormulaR1C1 = "=TODAY()" Range("A1").Select Selection.Copy Range("E1").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("F1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark2 .TintAndShade = -9.99786370433668E-02 .PatternTintAndShade = 0 End With Range("F1").EntireColumn.AutoFit ' Show Field List ActiveWorkbook.ShowPivotTableFieldList = True ' Other Sub Routines FilterPivotTable FormatPivotTable ------------------------- Set pc = Nothing Set pt = Nothing Set pf = Nothing

[@juanpablohorn6642]

@@WiseOwlTutorials No worries Andrew! I tried the suggestions you entered here, but I still get a run time error while executing the code. The good news is, I solved the issue. I used the following code: Sub CreatePivotTable() Dim pc As PivotCache Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet Dim SourceData As String Application.ScreenUpdating = False ThisWorkbook.Activate DeleteAllButTMSheet WeeksInOD CountIL1AndIL2 ' Sheets.Add Sheets.Add After:=TM SourceData = TM.Name & "!" & TM.Range("Ini").CurrentRegion.Address If ThisWorkbook.PivotCaches.Count = 0 Then Set pc = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Chr(34) & SourceData & Chr(34), _ Version:=xlPivotTableVersion15) Else Set pc = ThisWorkbook.PivotCaches(1) End If Set ws = ActiveSheet ws.Select ws.Name = "PivotTable" Range("A3").Select ' Create Pivot Table ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Top Measures!R1C1:R10000C26", Version:=6).CreatePivotTable TableDestination _ :="PivotTable!R4C2", TableName:="TopMeasures", DefaultVersion:=6)------>I recorded a macro creating a pivot table Set pt = ActiveSheet.PivotTables(1) ' Add Table Rows Set pf = pt.PivotFields("Project #") pf.Orientation = xlRowField Set pf = pt.PivotFields("Project Name") pf.Orientation = xlRowField ' Add Table Values Set pf = pt.PivotFields("Weeks in OD") pf.Orientation = xlDataField Set pf = pt.PivotFields("IL") pf.Orientation = xlDataField pf.Function = xlCount pf.Caption = "Number of IL" ' Add Table Filters Set pf = pt.PivotFields("PC") pf.Orientation = xlPageField Set pf = pt.PivotFields("Subproject") pf.Orientation = xlPageField Set pf = pt.PivotFields("IL") pf.Orientation = xlPageField ' Add Todays Date ws.Range("E1").Value = "Date" ws.Range("F1").FormulaR1C1 = "=TODAY()" ws.Range("F1").Value = ws.Range("F1").Value Range("A1").Select Selection.Copy Range("E1").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("F1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark2 .TintAndShade = -9.99786370433668E-02 .PatternTintAndShade = 0 End With Range("F1").EntireColumn.AutoFit ' Show Field List ActiveWorkbook.ShowPivotTableFieldList = True FilterPivotTable FormatPivotTable Set pc = Nothing Set pt = Nothing Set pf = Nothing Application.ScreenUpdating = True End Sub

[@WiseOwlTutorials]

@@juanpablohorn6642 Hi Juan Pablo, it seems that I was wrong about the PivotTable version options not allowing 6 as a value, it appears that Microsoft haven't yet updated their documentation to include it! docs.microsoft.com/en-us/office/vba/api/excel.xlpivottableversionlist So I've removed my reply on that. The addition of the Chr(34) expressions to add " " characters would still cause a problem if that first pivot cache was ever used. But the way your code works now means that the first pivot cache will never be used - your code creates a new pivot cache each time you add a pivot table. You could delete the IF statement which checks for the existence of a pivot cache. Alternatively, you could use something similar to this: (I hope it helps!) Sub CreatePivotTable() Dim pc As PivotCache Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet Dim SourceData As String Application.ScreenUpdating = False ThisWorkbook.Activate DeleteAllButTMSheet WeeksInOD CountIL1AndIL2 SourceData = TM.Name & "!" & TM.Range("Ini").CurrentRegion.Address If ThisWorkbook.PivotCaches.Count = 0 Then Set pc = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=SourceData, _ Version:=6) Else Set pc = ThisWorkbook.PivotCaches(1) End If Set ws = Sheets.Add(After:=TM) ws.Select ws.Name = "PivotTable" Range("A3").Select ' Create Pivot Table Set pt = pc.CreatePivotTable( _ TableDestination:="PivotTable!R4C2", _ TableName:="TopMeasures", _ DefaultVersion:=6)

[@juanpablohorn6642]

@@WiseOwlTutorials Hi Andrew. Thanks again for your reply. I implemented the changes you mentioned above, and i get a pretty weird set of run time errors. The first one says: "The PivotTable field name is not valid. To create a pivot table report, you must use data that is organized as a list labeled with columns. If you are changing the name if a pivot table field, you must type a new name for the field". The error this occurs on is the one where I set pt=pc.CreatePicotTable(). Then, if I try running it again, I get the following error: "Method 'CreatePivotTable' of object 'Pivotcache' failed. This also refers to my set pt=pc.CreatePivotTable. Finally, if I try running it again, Exce closes completely. I'm really not sure why the code you are using works for you. Even the code for this video's Excel file has no issue whatsoever. Either way, thanks a lot for taking your time to reply to my questions! I really appreciate it!

[@BikerBabumoshai]

Hi I'm facing issue while renaming the pivot Table (current office in use is Office365). The error number is 1004. it says The pivot table name is not valid To create a Pivot table report, you must use the data that is organized as a list and labelled in col. the same data set when im using with your file is running file but after typing the code in a different excel(even tried pasting your code into another sheet) it doesn't work. Please help on this.

[@WiseOwlTutorials]

Hi! It sounds as though your data isn't arranged correctly. Check that your data is arranged as a table in columns and with a single header row. There should be no completely blank rows or columns and each column should have a name. I hope it helps!

[@007tushar]

Hi. I tried using your code exactly but my pivot table is not getting created. It is not throwing any error as well. Can you please help me?

[@romanemul1]

so it means that the pivotcache is like counting mechanism and pivottable with fields and items is the interface. Is that right ?

[@TheSardOz]

Hi Andrew, I want to change the the value of the top 10 items filter from a different sheet from were the pivot table is. This code work fine if I am in the pivot table sheet but if it call the subroutine when I am in a different table it wont work.. Sub ResetTop20() Dim pt As PivotTable Set pt = ActiveWorkbook.Worksheets("DashboardPivots").PivotTables("PerformanceBySymbolTop20") pt.PivotFields("SecCode").ClearValueFilters pt.PivotFields("SecCode").PivotFilters. _ Add2 Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PerformanceBySymbolTop20"). _ PivotFields("Sum of Net P&L"), Value1:=3 End Sub what I am doing wrong ?

[@WiseOwlTutorials]

Hi! It looks like you're referring to the ActiveSheet in the DataField parameter so if you're on a different worksheet to the one containing the pivot table it won't work. I hope it helps!

[@TheSardOz]

@@WiseOwlTutorials how can I reference a different sheet ?

[@WiseOwlTutorials]

@@TheSardOz Hi! If you want the basics of referencing different sheets take a look here kzbin.info/www/bejne/emLHf415bc6HmNE In your example you could just use your pivot table variable - replace this ActiveSheet.PivotTables("PerformanceBySymbolTop20") with pt I hope it helps!

[@TheSardOz]

@@WiseOwlTutorials that was exactly the problem. Thank you 🙏..

[@WiseOwlTutorials]

@@TheSardOz No problem, glad you sorted it!

[@gracege3836]

Hi this is very helpful, but i'm getting run-time error 5 at 8:14 and not sure why, googled it but seems no help, could you suggest anything please?

[@WiseOwlTutorials]

Hi Grace, I'm sorry but I'm not sure why you receive that error. You could try downloading the working example using the link in the video description and check if you still receive the same error.

[@SP-tx7tc]

Hi Sensei, how can I use xldistinct count function in pivot or the other ways in data field function

[@theguildedcage]

What are some typical causes of Run-time error '1004': The Pivotable name is not valid.To create a PivotTable report,......? I've cleaned the data, that is to say, there are no blank rows or columns. The source data is formatted as a table called "JOB_OPERATIONS" Thanks for the support!

[@theguildedcage]

So, I created a fresh data source that is not formatted as a table and all is working well. Since my source data is formatted as a table and connected to an external data source, I'll have to learn how to take that into consideration. I think you mentioned you go over that in later videos. Thanks, -R

[@juanpablohorn6642]

Hello Andrew! It's me again. I have yet another question. I was trying to filter a pivot table while looping through a calculated field, but unfortunately, I had no success. Would you happen to know how to loop through a calculated field and filter out the zeros? This is the code I'm currently using: Set pt = ActiveSheet.PivotTables("SEKTotalCostGraph") Set pf = pt.PivotFields("Sum of Total Cost in Thousands") For Each pi In pf.PivotItems If pi.Value = "(blank)" Then pi.Visible = False End If On Error Resume Next If pi.Value = 0 Then On Error GoTo 0 pi.Visible = False Else pi.Visible = True End If Next pi

[@WiseOwlTutorials]

Hi Juan Pablo! I don't have a quick answer to this one, I'll have to look into it. I'm teaching at the moment but I'll try to get chance to take a look at the end of the day!

[@juanpablohorn6642]

@@WiseOwlTutorials No worries Andrew! I really apprexiate tour help!

[@WiseOwlTutorials]

@@juanpablohorn6642 Hi Juan Pablo, I would first take a look at the section of the video starting at 38:54. It looks as though you're trying to apply your filter directly to the data field rather than adding a filter to the row or column group field. If you imagine a pivot table similar to the one shown in the video with Genre (Action, Adventure, etc.) as the row groups and Sum of Oscar Wins as the data field. To filter out the genres with 0 Oscars we would need to do something like this: Set pf = pt.PivotFields("Genre") pf.PivotFilters.Add2 _ Type:=xlValueDoesNotEqual, _ DataField:=pt.DataFields("Sum of Oscar Wins"), _ Value1:=0 So the PivotField variable refers to the Genre field. The PivotFilter refers to the Sum of Oscar Wins data field to check that the value is not equal to 0. This will remove any genres where the sum of Oscar wins is 0. I hope that helps!

[@juanpablohorn6642]

@@WiseOwlTutorials Hi Andrew! Actually, I have already tried this kind of filtering before, but when I try this, I get a run time error saying "Application-defined or object-defined error". So I'm not sure why this method is not working either. Actually, on what I'm trying to do, I have two pivot tables, and the first one I had no issued filtering with the for each loop I shared with you before, but in the second one which is the one I have the calculated field for It doe snot work. I'm really confident that the calculated field is the main issue here. This is my whole sub routine: Sub FilterPivotTable() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim df As Object Set pt = ActiveSheet.PivotTables("SEKTimePerPhaseGraph") Set pf = pt.PivotFields("Hours") For Each pi In pf.PivotItems If pi.Value = "(blank)" Then pi.Visible = False End If On Error Resume Next If pi.Value = 0 Then On Error GoTo 0 pi.Visible = False Else pi.Visible = True End If Next pi 'THE FOLLOWING CODE IS THE ONE WHICH IS NOT WORKING Set pt = ActiveSheet.PivotTables("SEKTotalCostGraph") Set pf = pt.PivotFields("Sum of Total Cost in Thousands") ' pf.PivotFilters.Add2 Type:=xlValueDoesNotEqual, DataField:=pt.DataFields("Sum of Total Cost in Thousands"), Value1:=0 ' For Each pi In pf.PivotItems ' ' If pi.Value = "(blank)" Then ' ' pi.Visible = False ' ' End If ' ' On Error Resume Next ' If pi.Value = 0 Then ' On Error GoTo 0 ' ' pi.Visible = False ' ' Else ' ' pi.Visible = True ' ' End If ' ' Next pi End Sub

[@WiseOwlTutorials]

@@juanpablohorn6642 Hi Juan Pablo! You can filter on a Calculated Field but as far as I know you can't refer to the PivotItems of a calculated field because it doesn't have any PivotItems. As per Microsoft's documentation "The items are the individual data entries in a field category." A Calculated Field is computed by the pivot table in the Values area, it doesn't have any data entries or Items to reference. A Calculated Item would have PivotItems which you could change the Visible property of but that's not what you have here. I've created a Calculated Field which divides the Run Time field by 60 and added this to the Values area of my Pivot Table. I can filter the Items of the Genre field based on the value of the Calculated Field like so: Sub FilterCalculatedField() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables("MoviePivot") Set pf = pt.PivotFields("Genre") pf.ClearAllFilters pf.PivotFilters.Add2 _ Type:=xlValueIsGreaterThanOrEqualTo, _ DataField:=pt.DataFields("Sum of Run Time Hours"), _ Value1:=100 End Sub This works as expected. Note that the pf variable refers to the category field whose items I want to filter based on the value of the Sum of Run Time Hours data field. Other than that, I'm sorry but I don't know why it isn't working for you. I hope that helps you find the answer!

[@qhdtn6412]

Dear teacher, I'm creating a pivot table with the code I've learnt, encounter run time error message. Can you please advise me where I need to review? My code is as below, error raised when the last line below is excuted, "Run-time error '5'Invalid procedure call or argument" Dim pc As PivotCache Dim pt As PivotTable Dim pf As PivotField If ThisWorkbook.PivotCaches.Count = 0 Then Set pc = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=ActiveSheet.Name & "!" & ActiveSheet.Range("N5").CurrentRegion.Address, _ Version:=xlPivotTableVersion15) Else Set pc = ThisWorkbook.PivotCaches(1) End If Range("S5").Select Set pt = pc.CreatePivotTable( _ TableDestination:=ActiveCell, _ TableName:="PayListPivot")

[@victoropere2107]

awesome

[@markbondorphfloor3035]

Hi. Frist of all. Thank you for making these turtorials. They are nothing but amazing. I'm currently having some issues with my own data set. When activating the procedure that creates the caches and the pivot table run-time error 1004 occurs. The code is AS yours and works when it is copied into Tour exercise sheet which i found on your webpage. On Microsofts webpage Microsoft suggests as a solution that all headers should not be left blank. But non of the headers are blank in my data set. Do you have any suggestions on how to solve this? Thank you very much.

[@markbondorphfloor3035]

Thank you for the fast reply. I actually just solved it. The problem occured becase the data source sheets (Name) was Sheet1. Is there a way to chance the (Name) using VBA? I've only found a way to change the Name and not (Name).

[@markbondorphfloor3035]

Sorry... It was just the sheet name not (Name) that made the error occur.

[@manishpanwar1421]

Sir, I tried sorting data through Grand Total but couldn't. I'm able to to with the name of PT.Pivotfields, but I need to sort in descending order for Grand Total. pls help

[@WiseOwlTutorials]

Hi Manish! Stackoverflow is your best friend for questions like this stackoverflow.com/questions/39315766/sorting-on-row-totals I hope it helps!

[@manishpanwar1421]

Yup!! Thank you sir ☺🙏

[@sukumarreddyduvvuru3201]

Is there a way to select a couple of genre from the movie list?

[@WiseOwlTutorials]

Hi Sukumar! Yes, each genre pivot item can be made visible or invisible independently.

[@FabirukaS]

I'm trying to create another pivot table in the same sheet that the one i've already created, however, i get a mistake at the moment to use the command createpivottable. i created th pcache for another sheet and seem not problem, just at the momento of creating it, help

[@FabirukaS]

Hi, i solved the past problem the only one that is left is that, i cannot use the name of the Sheet for the Source data, it only allows me to put the Sheet# instead the name of the sheet. the sheet is called Loaded and, when i use the name it mark error Set pcl = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Sheet2.Name & "!" & Sheet2.Range("A1").CurrentRegion.Address, _ Version:=xlPivotTableVersion15)

[@KayYesYouTuber]

How to get a list of all selected Pivot items for a Pivot Field with VBA?

[@WiseOwlTutorials]

Hi Kay, you can see some sample code in the question posted here stackoverflow.com/questions/36809174/list-visible-pivot-items-with-excel-formula I hope that helps!

[@pamelastreet5214]

Where is the link to get the files?

[@stamatarsenikos9708]

copied the code exactly up to creating pivot table and it doesn't work

[@claudiurusu1952]

For all my fellow friends that struggled with the error: "pivottable name is not valid". For me worked when I deleted all the spaces from the name of the source sheet.

[@WiseOwlTutorials]

Thanks Claudiu, that's a useful tip!

[@funniq]

I wanted to - finally - buy you tea and biscuits, but i get an error unfortunately ... Our web server has a problem with the web page at /Donate/ReceiptPaypal?CartId=xxxx&paymentId=PAYID-LTZL7II7xxxxxxx5P&token=EC-4NC67xxxxx04&PayerID=xxxxxxxxxxxxxx. If you followed a link on our website, we'd love to help you track down the cause of the problem. Please send us a message quoting error number 23587 and giving your email address, and we'll get back to you soon. Apologies for the inconvenience!

[@funniq]

Fortunately PayPal confirmed me in e-mail that payment was succesful ... Please enjoy, hugs :)

[@nakuldas8207]

In file type .xlsb . This code giving error creating pivot table. If same data move to other sheet of the workbook, this working fine. Sub SummaryPvt() Dim pt As PivotTable Dim pc As PivotCache Dim ShtPvt As Worksheet On Error Resume Next Worksheets("Pvt Summary").Delete On Error GoTo 0 Set ShtPvt = Worksheets.Add ShtPvt.Name = "Pvt Summary" Set pc = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Sheet1.Name & "!" & Sheet1.Range("A1").CurrentRegion.Address, _ Version:=xlPivotTableVersion15) Set pt = pc.CreatePivotTable( _ TableDestination:=ShtPvt.Range("A5"), _ TableName:="MissingGSTTbl") Match Supplier Vendor Amt Missing in 2B GUPTA ENTERPRISES CGUP0009 123 Missing in 2B GUPTA ENTERPRISES CGUP0009 623 Missing in 2B M.K.PEACOCK MINERAL CMKP0002 1123 Missing in 2B NETWORK TRANSPORT CNET0007 1623 Missing in 2B NETWORK TRANSPORT CNET0007 2123 Missing in 2B NETWORK TRANSPORT CNET0007 2623 Missing in 2B SALWAN PRODUCTS CSAL0025 3123 Missing in 2B Swastik Medica CSWA0070 3623 Missing in 2B VIKAS TRADERS CVIK0003 4123 Missing in 2B GLOBAL AIRCON CGLO0098 4623 Would any one give solution. Thanks in advance .