Automate Excel Pivot Table with VBA | Create A Pivot Table | Lesson 1

Просмотров 31 тыс.

% 249

In this tutorial series, you will learn how to automate Pivot Table in Excel using VBA.
Pivot Table is probably one of the most useful and powerful features in Excel, especially for reporting and data analysis. Using pivot table, we can extract useful information that is hard to see from a raw data source itself. And VBA, being the built-in programming language of an Excel application, which allow us to automate and streamline many things we do with Pivot Table.
How to enable Developer Tab: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-fPPLhQOqj4s.html
Download Financial Sample Excel Workbook: docs.microsoft.com/en-us/power-bi/create-reports/sample-financial-download
Source Code: learndataanalysis.org/automate-excel-pivot-table-with-vba/
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: ru-vid.com/show-UCKpLA4muueRpBPMfZVHqKfw
Timestamp:
00:00 - Intro
00:40 - Where to download Excel data sample file
00:58 - Getting started with VBA script
04:18 - Define Pivot Table data source
07:39 - Define Pivot Table Cache
08:47 - Create Pivot Table
09:50 - Insert Pivot Table fields
Part 1: How to Create Pivot Table with VBA: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xDEjzI3R3yA.html
Part 2: How to configure Pivot Table options: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-b0AVq9_S-5I.html
Part 3: How to delete Pivot Tables: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vUSVVufrQOg.html
Part 4:: How to filter a Pivot Table report: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-yagt2uIqztk.html
Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------
🔑PayPal Me: www.paypal.me/jiejenn/5
🔑Venmo: @Jie-Jenn
💼Join Robinhood with my link and we'll both get a free stock 🤝 join.robinhood.com/jiej6
Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
Patreon: www.patreon.com/JieJenn
By shopping on Amazon → amzn.to/2JkGeMD
More tutorial videos on my website → LearnDataAnalysis.org
Business Inquiring: RU-vid@LearnDataAnalysis.org
PS: Due to the number of requests I get these days, I now charge $5.00 service fee to help you debugging your code. If you need my assistance, please shoot me an email at RU-vid@LearnDataAnalysis.org
#PivotTable #VBA #Excel #Reporting

Опубликовано:

7 июн 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 22

@stewartalexander158 2 года назад

I absolutely love that you use big data sets in your videos.

@patrickwheeler1979 Год назад

Jie this was absolutely fantastic! SOOOOO helpful, I learned a ton!

@dubsed Год назад

Thank you! I often figure stuff out myself by recording a macro and watching how Excel builds it. Unfortunately, I kept getting errors using the ws.PivotTables.Add method that macro used. This tutorial helped a lot! Thank you!

@pearsonyam4052 2 года назад

great stuff - been teaching myself vba over the past year and couldn't find a decent video on pivot tables until yours. subscribed.

@jiejenn 2 года назад

Thanks for subscribing. And glad my video helped.

@gopijanjanam985 Год назад

Thank God, I found this tutorial, thanks man you really did a great job

@jackfish1983 2 года назад

Super useful!! thanks!

@optimalbinary4183 6 месяцев назад

subed man realy insightfull.

@MegaKgee 11 месяцев назад

Hi - this training is EXCELLENT. I'm trying to have my vba create a pivot in the nearest blank cell, could you please help?

@sindhushagrandhi6623 2 года назад

Hi Jie, That was some great stuff that you posted. thank you. I have a question though- is it possible to give dynamic fields in the Pivot under columns section?? for Eg: as part of resource management, I want to create pivots every month and then the fields under the Column will change based on the month. How do I go about it then? I hope my question was clear? looking forward to your reply!

@jiejenn 2 года назад

You can, but the additional of code required is gonna be messy though.

@jannopotestades3205 2 года назад

I have a question from step 3, what is the purpose of "Sales Report XYZ"?

@adonisfilea2529 10 месяцев назад

It's great thank you when creating PTCache, run-time error '440' appears: Automation error

@saavedragabo Год назад

Hi Jie, Thanks for your video! I managed to replicate your procedure and get a nice pivot table! I wonder if it is possible to create multiple pivot tables for specific combinations of variables appearing one after the other (vertically) in the same sheet. For example using a list box where user can select one variable from a table (say Var1) and another listbox where user can select multiple variables from the same table (Var2, Var3, ...). Then creating a pivot table for each combination of those variables, where the single selected Var1 is always displayed on columns and the others horizontally. This will need to account for the size of every table so they dont overlap. I thought this may be a recurrent task but can't find any ressources out there. Thanks in advance!

@jiejenn Год назад

You can, but this is something pretty advanced, I won't be able to help in a comment unfortunately.

@saavedragabo Год назад

@@jiejenn Thanks for your reply Jie!

@dawncruz2686 2 года назад

Using Excel 2013: I am getting Error:Subscript out of range. Even though variables are declared. Any ideas? Thanks for the video.

@joshreinhardt7750 Год назад

This is definitely too late to help you but I was experiencing the same issue and then after an hour or so of troubleshooting I figured out my issue and it may be yours. In the example he has ThisWorkbook defined as wb. I should have had ActiveWorkbook defined as wb. Too long to explain here but just look up the difference between ActiveWorkbook and ThisWorkBook.