Dynamically Change the data Source of Pivot Table in Excel and VBA
#DynamicPivotTable
The video tutorial focuses on changing the data source of a pivot table in Excel.
I have four worksheets with different data sets.
The goal is to create an Excel table for each data set in the respective worksheets.
To start with I use the first sheet containing computer devices.
The process begins by pressing Control + T to open a dialog box for creating a table.
The table includes headers: date, item, quantity, rate, and total.
Click "OK" to create an Excel table.
Access the table design to remove the filter button.
Navigate to "Sheet2" for electronics and create another table using "Ctrl + T" and click "OK."
Remove the data filter and change the table style to black.
Move to the third sheet for electrical devices, use "Ctrl + T" to create a table, and click "OK."
Undo the last action if needed by clicking inside the table and using "Ctrl + T."
Start with an Excel table and select a new table style.
Choose a specific style, such as the fourth option for plumbing.
Use the shortcut Ctrl + T to create a new table and confirm by clicking OK.
Select another style, like a green one, to apply to the new table.
Repeat the process to create a total of four tables.
Name the first table "comp devices" and press Enter to save the name.
Navigate to the second sheet of the spreadsheet.
Change the name of the first table to "electronics" and press enter.
Move to the third table (table number four) and rename it to "electrical," then press enter.
Rename the next table to "plumbing" and press enter.
The final tables are now labelled "comp devices, “electronics” , "electrical" and “plumbing”
Start by selecting the data range for the pivot table.
Click on the "Insert" tab in Excel.
Choose "Pivot Table" from the options.
Select "From Table/Range" to create the pivot table.
Click "OK" to generate the new worksheet with pivot table fields.
To create a pivot table, click on the "item" field to add it automatically.
You can also drag fields, such as "date," into the pivot table.
Adding the "date" field generates additional fields like quarters and years.
You can remove fields by clicking on them.
To add values, click on fields like "quantity," "rate," and "total," which will automatically populate the values section.
A pivot table is created to display different values.
To move the pivot table to cell A1, click inside the table, go to "Pivot Table Analyze," and select "Move Table."
After moving, the pivot table will be positioned at A1.
To change the data source, rename cell H1 to "All Tables" to create a named range.
Start by naming the box and typing all table names.
Press enter to confirm the input.
Create data validation by pressing Alt + D, then Tab, L, Tab.
Enter the names of the tables: "comp devices," "electronics," "electrical," and "plumbing."
Creating a Macro for Data Validation
Begin by clicking "OK" to create data validation with table names.
Record a macro to generate code for changing the data source.
Access the macro recording feature by clicking the button or navigating to the developer tab.
Click "Record Macro" and then confirm by clicking "OK."
Actions performed in the Visual Basic Editor are recorded.
Navigate to the Pivot Table Analyze section.
Click on "Change Data Source" to modify the data.
For example, typing "electronics" updates the table to show electronics data.
To stop recording the macro, go to the Developer tab and click "Stop Recording."
Press Alt + F11 to view the generated macro code.
The macro code is being modified to change its name.
The new name for the range is set to "all tables".
The previous name is removed during this process.
The macro is renamed to "change data source".
Change the trusses to delete a specific one.
Introduce two lines for testing purposes.
Update the code to include plumbing devices.
Check the table for the plumbing devices.
Run the macro code by pressing F8.
Observe changes in the data after running the macro.
Dynamic Worksheet Changes in Macro Code
The text discusses making a worksheet more dynamic.
It involves selecting a specific sheet.
The focus is on the change event of the worksheet.
A macro code is called to enhance functionality.
The code includes a condition to check if the target address matches a specified range.
The text discusses the use of macro code in a worksheet.
It mentions testing the dynamic functionality of a pivot table.
Clicking on different categories (like electronics and electrical) updates the displayed data accordingly.
The focus is on ensuring that the pivot table reflects the selected category accurately.
The video discusses dynamic pivot tables and their design options.
Viewers are encouraged to interact with the content by subscribing and liking the video.
I would like to record my gratitude for the audience's attention and looks forward to future videos.