macro change pivot table data source

Excel VBA to Change Pivot Table Source (2 Easy Ways)

Suppose you need to work with a single criterion in a huge dataset. In that case, we use the pivot table to narrow down other criteria, but what if you already selected the criteria and now you have to change them? Starting the full process from the beginning is not an efficient way to do the work. So, you can use Microsoft Excel VBA to change the pivot table source. In this article, we will learn how to change pivot table data with a source using Excel VBA. So, let’s start.

The above overview video shows the output after changing the pivot table source. You will get appropriate and proper information once you go through the entire article.

What Is Pivot Table in Excel?

A pivot table is a tool in Excel where you can select the required data from a dataset and calculate, analyze, summarize, and get the data trend for better understanding. Pivot table charts are a visualization of the dataset summary. For instance, a company document contains cost, sales, and profit in one file, but you need to work on profit only. Here, you can create a pivot table with profit and work without the other hassles.

Excel VBA to Change Pivot Table Source: 2 Easy Ways

The dataset below represents the Product ID, Product, category, sales of the products , Date, and Outlets of the grocery shop, which is named ABC Groceries . This dataset shows the one-day average sale of a particular product in different outlets. Now, we are going to set this information in a pivot table so that we can get only the required information.

Sometimes, we need to change the source of the pivot table. So, follow the below methods to change the pivot table source using Excel VBA. Firstly, you need to open the VBA module. If you don’t know how to open the VBA editor, go through this article Open VBA window and insert New Module .

1. Using Excel VBA to Change Pivot Table Data in the Same Source Range

In this method, we will show you how to change pivot table data in the same source range. Here, the changed data will be fixed, and once you run the program, you cannot simply change the range that you already changed. To execute this simple method, follow the below steps.

• Initially, select cell range C4:E12 from the dataset.
• Therefore, select Insert >> Pivot Table from the Toolbar to get the Pivot Table dialog box .

• Select New Worksheet >> OK from the dialog box to get a new sheet.

• After that, select all the categories from the pivot table fields to get a pivot table with the required information.
• Change the name of the sheet to Pivot Table to avoid confusion during programming.

• Now, select Developer >> Visual Basic from the Toolbar or press ALT + F11 from the keyboard to get the Visual Basic module.

• Consequently, write down the code in the module as below.

Code Breakdown:

• This code starts with the subroutine ChangePivotTableDataSource where the pivot table is declared as pivotTable and changed range B4:G12 is declared as Range .
• Sheets(“Pivot Table”) refers the sheet to execute the process and (PivotTable1) specifies the pivot table.
• This part changes the data source of the dataset and create new pivot caches. Set source data range with a new range and end this program.

Once you complete the code, you can run the code and complete this procedure, but after clicking Run you need to go back to the sheet and check if the program runs correctly or not. To avoid this hassle, you can create a button with a simple click. Now act according to the following steps to add a button.

• In the beginning, select Developer >> Insert >> Button (Icon) from the Toolbar .

• Place the button in a random place, and the Assign Macro dialog box will pop up as below.
• Now, select the assigned macro and click OK .

• After assigning a macro, place the cursor on the button and right-click the mouse to get the option menu.
• Select Edit Text to change the button name.

You can change the format as well for a better look using the same procedure as Edit Text , but this part is completely optional.

• Finally, click the button to get the final output as below.

Now, if you change the data from B4:G12, you will get the new output as below.

2. Using Excel VBA to Change Pivot Table Data for Changing Source Range

In this method, we will change the pivot table data to change the source range. Let’s change the dataset ( Add a row) as below. Here we add a new column, which we will add to the pivot table as well. Now, go through the below steps to execute this process.

• Firstly, complete the procedure till write down the code applying the same method already shown in the previous method.
• Then write down the code in the Excel VBA module as below.

• This program starts with declaring the subroutine ChangePivotTableDataSource2 where the pivot table is declared as pivotTable, the changed new range is declared as Range, and the range address is declared as String .
• Now select a new range using the Input Box so that the range will be dynamic. If you input the wrong range VBA usually stops the procedure and terminates the program. So, On Error Go To 0 is used to work with the error and it will show “Invalid range” .
• Lastly, this part changes the data source of the dataset and creates new pivot caches. end this program by Setting the source data range with a new range.
• Therefore, add a button using the procedure already shown in the previous method.
• Finally, click the button to select the changed dataset and get the new output as below.

Things to Remember

• While working on Excel make sure your Excel supports VBA. If the VBA option is blocked then select File >> Option >> Trust Center >> Trust Center settings >> Macro settings >> Enable Macro >> OK .
• You can also add different criteria column-wise while working on changing the dataset range.

Download Practice Workbook

You can find the practice sheet here.

In this article, we learned how to change pivot table data from a source using Excel VBA . Here, we showed two different ways to solve this problem and covered everything regarding this issue. Hopefully, you can solve the problem shown in this article.

Related Articles

• How to Hide Source Data in Excel Pivot Table
• Pivot Table is Not Picking up Data in Excel

<< Go Back to Pivot Table Data Source | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

Tags: Pivot Table Data Source

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis,... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Changing data source of pivot tables with VBA

I'm looking for a code that can change the data source of my pivot tables from one sheet with a different data source. The data source sheet is identical just with different data.

I have a code that i've taken from the internet, but instead of changing the data source of my active sheet, it changes the data source on all my sheets.

Sadly i don't know much about VBA coding to know how to change the code to affect only my active sheet and i was hoping someone could help me.

Thank you in advance for the help!

• microsoft-excel
• pivot-table

2 Answers 2

This code will help you to change Source Data for all Pivot Table in particular Sheet.

How it works:

• Copy & Paste this Code as Standard Module .
• Sheet and Source Data names are editable.
• In this code MyData is a Named Range , that you need to create before you RUN this Code every time you want to change Source Data for Pivot Table .

• Thank you very much! The code that you gave me works. –  Pop Claudiu Apr 1, 2019 at 11:59
• Glad to help you @PopClaudiu ,, keep asking ☺ –  Rajesh Sinha Apr 1, 2019 at 12:00

I would like to split a document by destination (for example) and create automatically various specific files (for each destination) with the relevant pivot chart. How to do to update the data source of the new pivot charts each time (Spain, Italy, France...) ? As they keep the original data source with all destinations (Europe). Thanks

• As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center . –  Markus Meyer Dec 3, 2022 at 12:37
• If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review –  help-info.de Dec 3, 2022 at 12:53

You must log in to answer this question.

Not the answer you're looking for browse other questions tagged microsoft-excel pivot-table ..

• The Overflow Blog
• What a year building AI has taught Stack Overflow
• Want to be a great software engineer? Don’t be a jerk.
• Featured on Meta
• New Focus Styles & Updated Styling for Button Groups
• Upcoming initiatives on Stack Overflow and across the Stack Exchange network
• Google Cloud will be Sponsoring Super User SE

Hot Network Questions

• Why do scientists use specialized units for distance when metric units are perfectly adequate?
• Can "sit" mean "receive no attention"?
• A small REPL in Python
• Concentration of probability in reinforcement learning algorithms
• What is the difference between mind and consciousness?
• What caused pink flares during the eclipse
• Co-Existing with a Highly Extroverted Leader and "Forced Fun"
• Book about teen girl living in a town by herself (with her dog) as everyone died of radiation poisoning
• Am I in my rights to ask a colleague to not take notes on everything I say outside a meeting?
• Was Eliphaz in Genesis related to Eliphaz in Job?
• Endomorphisms of simple dualizable objects in a linear abelian monoidal categories
• Right censoring deaths in length of stay studies
• Random height in face mode
• Fixed Repeating Output
• difference between 開発元 and 開発者
• How can I make a custom header in less? Or alternatively, how can I open two files simultaneously in less?
• How do I properly exit a program and return to the CCP in CP/M?
• Should I revise the sentence in bold?
• What is SpaceX doing differently with their falcon 9 so that it doesn't cost as much as the Space Shuttle?
• Is this formula already known?
• How can I test if lower values on my regression line come from a level of a grouping variable?
• AirBnB: cancel confirmed booking with low price as a host
• How does one import Natural numbers in Lean 4 -- unknown identifier 'ℕ'?
• Clifford group without the phase gate

• Advertise With Us
• Excel Forum
• Commercial Services

How to Dynamically Update Pivot Table Data Source Range in Excel

Currently, we can dynamically change or update pivot tables using Excel Tables or Dynamic Named Ranges . But these techniques are not foolproof. As you will still have to refresh the pivot table manually. If you have large data that contains thousands of rows and columns, excel tables will not help you a lot. Instead it will make your file heavy. So the only way remains is VBA.

In this article, we will learn how we can make our pivot table automatically change the data source. In other words, we will automate the manual process of changing data source to dynamically include new rows and columns added to source tables and reflect the change in pivot table instantly.

Write Code In Source Data Sheet

Since we want this to be completely automatic, we will use sheet modules to write code instead of a core module. This will allow us to use worksheet events .

If the source data and pivot tables are in different sheets, we will write the VBA code to change pivot table data source in the sheet object that contains the source data (not that contains pivot table). 

Press CTRL+F11 to open the VB editor. Now go to project explorer and find the sheet that contains the source data. Double click on it.

A new coding area will open. You may not see any change but now you have access to the worksheet events.

Click on the left drop down menu and select the worksheet. From the left drop down menu, select deactivate. You will see a blank sub written on the code area name worksheet_deativate. Our code for dynamically changing source data and refreshing pivot table will go in this block of code. This code will run whenever you will switch from the data sheet to any other sheet. You can read about all worksheet events here .

Now we are ready to implement the code.

Source Code to Update Pivot Table Dynamically With New Range

To explain how it works, I have a workbook. This workbook contains two sheets. Sheet1 contains the source data that can change. Sheet2 contains the pivot table which depends on the source data of sheet2.

Now I have written this code in sheet1's coding area. I am using the Worksheet_Deactivate event, so that this code runs to update the pivot table whenever we switch from source data sheet.

If you have a similar workbook, you can directly copy this data.I have explained this code works below.

You can see the effect of this code in gif below.

How does this code automatically change source data and update pivot tables?

First of all we used a worksheet_deactivate event. This event triggers only when the sheet containing the code is switched or deactivated. So this is how the code automatically runs.

Now to change the source data of the pivot table we change data in the pivot cache. 

A pivot table is created using pivot cache. The pivot cache contains the old source data until the pivot table is not manually refreshed or source data range is manually changed.

We have created references of pivot tables name pt, pivot cache named pc and a range named source_data. The source data will contain the whole data. 

To dynamically get the whole table as data range we determine the last row and last column.

 lstrow = Cells(Rows.Count, 1).End(xlUp).Row

 lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

Using these two numbers we define the source_data. We are definite that source data range will always start from A1.

Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))

Now we have the source data which is dynamic. We just need to use it in the pivot table.

We store this data in pivot cache as we know pivot cache stores all the data.

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)

Next we define the pivot table that we want to update. Since we want to update the PivotTable1 (name of pivot table. You can check the name of the pivot table in the analyze tab while selecting the pivot table.) on sheet1, we set pt as shown below.

Set pt = Sheet2.PivotTables("PivotTable1")

Now we simply use this pivot cache to update the pivot table. We use the changePivotCache method of pt object.

pt.ChangePivotCache pc

And we have our pivot table automated. This will automatically update your pivot table. If you have multiple tables with the same data source, just use the same cache in each pivot table object.

So yeah guys, this is how you can dynamically change data source range in Excel. I hope I was explanatory enough. If you have any questions regarding this article, let me know in the comments section below.

Related Articles:

How to Auto Refresh Pivot Tables Using VBA : To automatically refresh your pivot tables you can use VBA events. Use this simple line of code to update your pivot table automatically. You can use either of 3 methods of auto refreshing pivot tables.

Run Macro If Any Change Made on Sheet in Specified Range : In your VBA practices, you would get the need to run macros when a certain range or cell changes. In that case, to run macros when a change is made to a target range, we use the change event.

Run Macro When any Change is Made On Sheet | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.

Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.

The Worksheet Events in Excel VBA | The worksheet event are really useful when you want your macros to run when a specified event occurs on the sheet.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel. The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value * 1 × nine =

Related Excel Tips

How to Display the Top/Bottom 10 Items in Exc...

How to Create Pivot Tables in Excel...

How to use the Dynamic Pivot Table in Excel...

Excel Pivot Table...

Printing a PivotTable Report in Excel 2007...

• Basic Excel
• Excel 365 Functions
• Excel Business Templates and Dashboards
• Excel Dashboards
• Excel Date and Time
• Excel Errors
• Excel Functions
• Excel Functions List
• Excel General
• Excel Macros and VBA
• Excel Spanish
• Excel Text, Editing and Format
• Excel Tips and Tricks

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

• Excel Editing
• Excel Format

Get latest updates from exceltip in your mail.

Google serves cookies to analyse traffic to this site. Information about your use of our site is shared with Google for that purpose

Privacy Overview

Strictly necessary cookies.

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.

• Search forums
• Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

• If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
• Question Forums
• Excel Questions

macro to change data source for pivot table

• Thread starter JoeSarton
• Start date Aug 12, 2011
• Tags pivot table source data
• Aug 12, 2011

Hello - Hoping someone can help me with my question regarding source data and pivot tables. One a tab I have columns of data which is the source data for a pivot table I have just below. For the sake of this question, lets say all of this is on "Sheet1", the data is in cells A2:F25, and the pivot table is in cell G27. The problem comes when I make a copy of the tab (keeping the copied tab in the same workbook), change the data in this new tab (called Sheet2), the pivot table still uses the data from Sheet1 (it seems to automatically anchor the data source to that original tab). Is there a way that I can run a macro so that it will change the data source for the pivot table in Sheet2 to run off of the data in Sheet2. The reason I don't want to manually adjust the source data is that I will have many tabs and hoping there was a macro that I could run on each tab to change the data source. Hopefully this question is clear enough that someone can help. I don't know if it makes a difference, but I am in Excel 2010 Thanks!  

Excel Facts

Jerry Sullivan

Mrexcel mvp.

• Aug 13, 2011

Welcome to the Board! As a first step, I recommend you use dynamic named ranges as the source for your PivotTable data. The Contextures site has very clear instructions on how to do that. http://www.contextures.com/xlPivot01.html#Dynamic Next, if your source data is always on the same worksheet as your PT, then I'd suggest you define the named ranges with Worksheet scope (instead of Workbook scope) and adopt the practice of using the same name for the source data range on each sheet. In response to your question, if you do the two steps above, all you need to do when you copy a sheet with a PT is to remap the source data to the named range on the new sheet instead of the old sheet. For example, you have a PT on Sheet1 and your data source is range:Sheet1!PTsource When you copy it to make Sheet2, you just need to change the reference to: Sheet2!PTsource. You could do that manually or with a simple macro like the one below. Since your ranges are dynamic, they will adjust when you add or delete rows. Here is the code- it assumes you have only one PT per sheet. Code: Sub Update_PTSource() With ActiveSheet .PivotTables(1).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:="'" & .Name & "'!PTsource") End With End Sub  

• Aug 14, 2011

Jerry - Many thanks, this worked out quite well. Appreciate the speedy reply!  

• Jan 5, 2012

Can someone help me fix this: Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.PivotTables(1).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:="priorities") Loop ----------------------- I'm trying to loop through the pivot tables and change their sourcedata to priorities  

Found my own answer Dim ws As Worksheet Dim PT As PivotTable For Each ws In ActiveWorkbook.Worksheets For Each PT In ws.PivotTables PT.SourceData = "priorities" Next PT Next ws  

• Feb 27, 2012

Hi all, I am looking for something similar, but I would like to be able to set the datasource for all pivottables in a workbook according to the value in a cell. The cell contains a dropdown list of all available datasources (dynamic named ranges). So far, I have the following but it isn't working (Error Code 1004 This command requires at least two rows of data) Dim ws As Worksheet Dim PT As PivotTable For Each ws In ActiveWorkbook.Worksheets For Each PT In ws.PivotTables PT.SourceData = "Summary!$c$3" Next PT Next ws Any ideas? Thanks, Graham  

Try this..... Sub RefreshPivotTables() Dim ws As Worksheet Dim PT As PivotTable Dim SourceName As String SourceName = Worksheets("Summary").Range("C3") For Each ws In ActiveWorkbook.Worksheets For Each PT In ws.PivotTables PT.SourceData = SourceName Next PT Next ws ActiveWorkbook.RefreshAll End Sub  

• Feb 28, 2012

mike_romano

• Apr 25, 2013

UHCAron said: Try this..... Sub RefreshPivotTables() Dim ws As Worksheet Dim PT As PivotTable Dim SourceName As String SourceName = Worksheets("Summary").Range("C3") For Each ws In ActiveWorkbook.Worksheets For Each PT In ws.PivotTables PT.SourceData = SourceName Next PT Next ws ActiveWorkbook.RefreshAll End Sub Click to expand...

• May 27, 2013

Hi Guys. Re: macro to change data source for pivot table Sub TestingPivot() ' ' TestingPivot Macro ' test pivot ' Dim ws As Worksheet Dim PT As PivotTable Dim SourceName As String SourceName = Worksheets("SMS Data Input").Range("A2:AH2") For Each ws In ActiveWorkbook.Worksheets For Each PT In ws.PivotTables PT.SourceData = SourceName Next PT Next ws ActiveWorkbook.RefreshAll End Sub (Error type mismatch) Above is the code to Change Source Data to Range(A:AH) each time I input data into the source data sheet. My issue is that the pivot table requires the source data to be changed each time to Column A:AH and refresh but how can I automate this as I have more that 1 pivot table on 4 sheets(using the same source data as referrence). I also dont want the pivot table to change its organized structure in terms of what I am currently comparing on the pivot. I am new to VBA and would appreciate your advice. Thanks Vince  

Similar threads

• Mar 15, 2024
• ItalianPlatinum
• Mar 25, 2024
• Feb 1, 2024
• Mar 27, 2024

• Feb 16, 2024

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

Spreadsheet Vault

Financial modelling, Modano financial models and Excel spreadsheets

Excel VBA Dynamic Ranges in a Pivot Table

Goal: A macro to allow Excel users to dynamically update a change source data range in a Pivot Table.

This tutorial will explore one way to creating a macro-based solution for managing dynamic source data ranges in a Pivot Table. As we can see we have a simple data array of business units by month by sales in Sheet1, which is referencing a pivot table in Sheet2.

The challenge of managing dynamic ranges was covered in an earlier post.

However, how can we achieve automation to reflect a changing source data range? What happens if we add some more financial data?

A standard source data range like in this Pivot Table will be unable to update for this expanded source data range. We can evidence this when we try to refresh the data.

Hence we need to write a macro to achieve this.

Writing the macro

Remember to insert a module. Notate your macro with a brief description. I will call this macro, dynamicPTRange

Firstly we will disable these following Excel properties, in order to improve the macro’s performance. Next, we need to declare the object variables of the macro. Some of these basic learnings were covered in the earlier video, Excel VBA Dynamic Ranges .

It is vital to accurately declare these object variables, otherwise the dynamic PivotTable range macro might not work properly. Similar to the dynamic ranges macro video , we need to set certain objects – as in the above screenshot.

First our two worksheets, Sheet1 containing the source data and Sheet2 with the destination PivotTable. It is wise to fix the name of the PivotTable to “PivotTable1”.

“startCell” needs to be explicitly stated too. The source data clearly begins in cell address “A1”.

Completing the Dynamic Ranges Pivot Table macro

Next the macro must identify the last row and last column in the source data range.

Now we have identified the last row and last column in the source data, we can apply it to the rngPivot object variable. This next piece of code will be applied into the Pivot Table as the dynamic source data range.

It is always a good approach in a macro to add an Error Handler, as it will help to minimize the chance of macro errors. This error handler will stop the macro from proceeding if one of the headings is blank. Whenever writing in a MsgBox into a macro, it is vital to ensure it is 100% accurate, otherwise you might incur a macro error.

This final part of the code will update or amend the Pivot Table’s source data range.

It does this in part by the use of the ChangePivotCache syntax. Then refreshing the Pivot Table with this piece of code. Next we can re-enable the Excel properties, which were earlier disabled because our macro is complete.

Let’s now review the macro.

Running the macro

We are ready to run the macro, by adding some financial data. As we can see the macro is correctly updating the Pivot Table based on the dynamically changing source data.

How about if we want to delete some of this source data? The macro is correctly making this update in the Pivot Table.

Here is the example workbook, which contains the Dynamic Ranges in a Pivot Table macro.

• Intermediate Macros

VBA to Change Pivot Table Source

I recently worked on a project that had three distinct and large datasets for different geographical areas in three workbooks.  Starting with the first workbook and data set in a Table in its own worksheet, I created a worksheet of seven Pivot Tables to provide seven Pivot Charts needed in their own sheet.  This could also easily suit a Dashboard solution by making the Pivot Charts smaller.  The charts had to be the same for the other two datasets and the columns in the datasets were identical.

[vb] Sub Change_Pivot_Source() ‘For when you copy say a worksheet of Pivot Tables to another workbook Dim pt As PivotTable Sheet6.Activate For Each pt In ActiveWorkbook.Worksheets(“Pivot Tables”).PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=”tblData_y”) Next pt

End Sub [/vb]

In this specific situation, Sheet6 was the new copied sheet and “tblData_y” is the Table name of the data to be used in the Pivot Tables so adapt for your situation.

So this worked well.  I enjoy writing what I call ‘quick and dirty’ macros for my projects so long as it saves the client time as you often learn something new in VBA with every new code and of course you have the code then for future situations.  Plus VBA is fun!

Change the source data for a PivotTable

After you create a PivotTable, you can change the range of its source data. For example, you can expand the source data to include more rows of data. However, if the source data has been changed substantially—such as having more or fewer columns, consider creating a new PivotTable.

You can change the data source of a PivotTable to a different Excel table or a cell range, or change to a different external data source.

Change the source data

Click the PivotTable report.

On the Analyze tab, in the Data group, click Change Data Source , and then click Change Data Source .

The Change PivotTable Data Source dialog box is displayed.

Do one of the following: To use a different connection

Click select a Use an external data source , and then click Choose Connection .

The Existing Connections dialog box is displayed.

In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).

Select a connection from the Select a Connection list box, and then click Open . What if your connection is not listed?

Note:  If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.

For more information, see Manage connections to data in a workbook .

To change the data source of a PivotTable to a different Excel table or a cell range

Click Select a table or range , and then enter the first cell in the Table/Range text box, and click OK.

What if your connection is not listed?

If your connection is not listed in the Existing Connections dialog box, click Browse for More , and then look for the data source you want to connect to in the Select Data Source dialog box. Click New Source if appropriate, and follow the steps in the Data Connection Wizard , and then return to the Select Data Source dialog box.

If your PivotTable is based on a connection to a range or table in the Data Model , you can choose another Data Model table or connection on the Tables tab. However, if your PivotTable is based on the Workbook Data Model , you can’t change the data source.

Pick the connection you want, and click Open .

Click Only Create Connection .

Click Properties , and click the Definition tab.

If your .odc connection file has been moved, browse to its new location in the Connection file box.

If you need to change values in the Connection string box, contact your database administrator.

On the Options tab, in the Data group, click Change Data Source , and then click Change Data Source .

The Change PivotTable Data source dialog box is displayed.

Do one of the following:

To use a different Excel table or cell range, click Select a table or range , and then enter the first cell in the Table/Range text box.

To use a different connection, click select a Use an external data source , and then click Choose Connection .

Select a connection from the Select a Connection list box, and then click Open .

For more information about what data sources are supported, see  Import and shape data in Excel for Mac (Power Query).

To change the data source of a PivotTable to a different Excel table or a cell range, click Select a table or range , and then enter the first cell in the Table/Range text box, and click OK

To use a different connection, do the following:

Do one of the following: 

Type in the desired Table / Range in the Source box. 

Tap on the Source  box, and then select the desired Table/ Range on the grid.

4.   Tap Apply .

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Create a PivotTable with an external data source

Create a PivotTable connected to Power BI Datasets

Manage connections to data in a workbook

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Contextures Blog

Excel tips and tutorials

Change Data Source for All Pivot Tables

If you want to change the data source for a single Excel Pivot Table , you can use a command on the Ribbon. If you want to change data source for all pivot tables in a workbook, you can use a macro, instead of making the changes manually.

Video: Change Pivot Table Data Source

In this video, you’ll see how to locate the pivot table data source, then check that data source, to make sure it includes all the rows and columns that you need. If needed, adjust the data source, to include new rows or columns.

• NOTE: For a long term solution, use a named Excel table , as a data source. It will adjust automatically, if new rows are added.

To follow along with the video, you can download the sample file from the Pivot Table Source Data page on my Contextures site.

The full video transcript is at the end of this page.

Video Timeline

• 00:00 The Orders Pivot Table
• 00:21 Manually Check the Numbers
• 00:33 Find the Source Data
• 01:08 Change PivotTable Data Source Window
• 01:27 Fix the Data Source Range
• 01:57 Create a Dynamic Source
• 02:48 Create a Named Table
• 04:22 Use Dynamic Source for Pivot Table
• 04:56 Test the Dynamic Source
• 05:31 Conclusion

Change Data Source One Pivot Table

Follow these steps, to change the data source for a single pivot table.

• Select a cell in the pivot table that you want to change
• On the Ribbon, under PivotTable Tools, click the Options tab
• Click the upper part of the Change Data Source command

• When the Change PivotTable Data Source dialog box opens, press the F3 key on the keyboard, to open the Paste Name window.
• Click on the named range that you want to use, and click OK

• Click OK to close the Change PivotTable Data Source dialog box.

Change All the Pivot Tables in the Workbook

If you have several pivot tables in a workbook, and want to change all of them to a new data source, you can use a macro, instead of making the changes manually.

I’ve added a new page on the Contextures website – Excel Pivot Table Data Source – with sample code to update all the pivot tables.

The macro adds a sheet to your active workbook, showing a list of the file’s named ranges. Then, the macro prompts you to enter one of those names, as the new data source for all the pivot tables.

____________

Video Transcript: Locate and Change Excel Pivot Table Data Source

In this video, you’ll see how to find the source data for a pivot table and fix that source data, if there’s a problem getting the new or changed data that you’ve entered. In this pivot table, I’m showing orders. One of the products we sell is paper, and I entered a new order, with 200 as the quantity, and it’s not showing up here.

If I right click and refresh, it keeps showing 20 and 10 so it’s not picking up that new record. I’m going to find the source data and see if there’s a problem.

To find the source data, I have the cell selected in the pivot table. Up on the Ribbon, I can see Pivot Table Tools, and I’m going to click Analyze. If you’re using Excel 2010, this would say Options.

I’ll click here, and here’s the Change Data Source. There’s also a drop down list, but it’s quicker to just click at the top of this.

That opens up the Change PivotTable Data Source window.

It’s showing that there’s a range selected, and I can see those little moving lines in the background, and we can see that one row is not included here.

It stops at row 9 and the new record that I put in is row 10. That’s where my 200 is, and it’s not being picked up.

To change this and fix the problem, I can adjust the range that’s included here. So I could click, just back space, and type 10, instead of 9.

Or I can select all of this and delete it.

Then on the worksheet, select the exact range that I want to use. Then click OK, and when I go back to the pivot table, instead of 20, it’s now showing 220, so it’s picking up the new data.

You could continue to adjust that range as you add new rows to the data source.

But a better solution is to create a dynamic source for your pivot table, which will adjust automatically, if you add new records.

It’s easy to do that, if you’ve got Excel 2007 or later. I’m going back to the Orders sheet.

This is just a list that’s typed on the worksheet. We’ve got column headings and a row for each order, but if I look up on the ribbon, there’s no extra tab at the top.

We’re going to create a named table. It will be a special feature, and when I select a cell, if it’s a named table, I’ll see another toolbar that I can use, to work with that table.

To create the table, it’s quick and easy. Just select a cell in your list and on the Ribbon click Insert, Table.

It does a very good job of detecting the range, but if it didn’t select all the cells, you can adjust what’s typed in here.

Make sure that the check mark is here for My Table Has Headers, and then click OK.

You’ll get a formatted table. I’ll just click on one cell.

I don’t usually like the formatting that it puts on the table at first, but you can go up to the Design tab, there’s that Table tab that is appeared now, and select something that’s simpler and easier to read.

The next thing you should do right away is change the name of the table. It will give a default name that ends with a number.

Just select that and I’m going to call this tbl, for table, tblOrders, and press Enter.

So now we have a table name, and I can see that name,

if I go to the Formulas tab, Name Manager.

And there’s a list of all the names in this workbook. So it makes it easy to spot this, if you have several tables in your workbook.

I’ll close this, and the next step will be to use our name, so going back to the Table Tools, we’re going to use this name, tblOrders, as our source for the pivot table.

I’m going to just click in here, and then Ctrl+ C to copy that name.

I’ll go back to my pivot table now. And then with a cell selected in the pivot table, I’ll go back to the Analyze tab, click Change Data Source, and here’s the current data source.

I’m going to use Ctrl+ V to paste what I copied as the table name. So it’s now going to use this dynamic range which will adjust as we add new records.

Click OK, and nothing looks different here, but I’m going to go and add another order.

Just copy what we have above, by using Ctrl and the double quote. I’ll put in 100, so now we should see 320, where we had 220 before.

Going back to Pivot Sales, it still says 220. I’ll refresh, and now we’ve got 320.

So I didn’t have to adjust the range of the pivot table source data. That adjusted automatically, because it’s a named table.

So if your pivot table data will change frequently, make sure you use a dynamic source, like a named Excel table, and it will adjust as you add or delete records.

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website, at www.contextures.com

13 thoughts on “Change Data Source for All Pivot Tables”

Hi, Note tha this only works if the Pivot table and the database referenced by the Name, are in the same sheet. Regards!

May I retract on what I’ve said? It finally worked after taking a look to your code… My apologizes. Kind regards, Ezequiel

Also note that it doesn’t work unless you delete all your slicers.

How could it work without deleting slicers?

Found this solution to be very helpful for a workbook that contains 35 worksheets and 105 pivot table all drawn from the same data set (lots different views of the data needed). However, I seem to have run across some limitation either in Excel or possibly insufficient internal memory (2Gb currently). When I attempt to use the macro on the original file, only 72 of the 105 pivot tables get updated, the remaining pivot tables have the original data source. It also causes the display to behave unusually and I am unable to save the file. As an experiment I split the workbook, so that about have the pivot tables was in part 1 and the other half in part 2. Both halves contained the data set. When I applied the macro to the split workbooks, all the pivot tables are updated and I don’t have the problem with the display or saving the file. The original file had slicers for some of the pivot tables, but I removed those before first attempting to apply the macro to the entire file.

Works perfectly!!! Would really appreciate having comments for each major step explaining what the code is actually doing. Many thanks anyhow!

This doesn’t work for me. The window pops up and the new sheet is in the background, but there are no listed connections. All my connection are to a sql database if that matters. For example 10.xx.xx.xx Gravic TempReporting

New to macros, trying to make this work for my file. My data source is another workbook…how do i make this work where the data source is external?

hey Debra, thanks for the great macro- works really well ! Do we have a way to change the data source to a range instead of a named range? I ask because I need to setup slicers connected to all tables- and it doesn’t give me an option to connect it to multiple tables- even though the source data is the same.

Macro has been very helpful. Ran into the same Excel resource error as a prior user has had. Split the workbook into multiple workbooks and ran the macro on them seperately!

Thanks for the tip. Very useful. Saved me time.

Love this. I was able to modify two of your scripts to suit my needs. What I don’t understand is why the “List” script showed a bogus range in the source file, (usually C1:C70) rather than the actual source range ($A:$BR). No matter. It works!

Hello, I would like to split a document by destination (for example) and create automatically various specific files (for each destination) with the relevant pivot chart. How to do to update the data source of the new pivot charts each time (Spain, Italy, France…) ? As they keep the original data source with all destinations (Europe). Thanks

Dim pt As PivotTable Dim MyData As Excel.ListObject

Set MyData = Application.Range(Europe).ListObject

For Each pt In ActiveWorkbook.Worksheets(“Chart – City”).PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=”MyData”) Next pt

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Data Management

D-Back  for iOS/Win/Mac - Data Recovery HOT

Recover Deleted Data from iOS/Win/Mac

Data Recovery

D-Back  Hard Drive Recovery - Hard Drive Data Recovery

Recover Deleted Data from PC/Mac

Windows/Mac Data Recovery

ChatsBack  for WhatsApp - WhatsApp Recovery

Recover Deleted WhatsApp Messages

WhatsApp Messages Recovery

ChatsBack  for LINE - LINE Recovery

Recover Deleted LINE Messages

LINE Messages Recovery

iMyTrans - Apps Data Transfer HOT

Transfer, Export, Backup, Restore WhatsApp Data with Ease

WhatsApp Data Transfer Tool

iTransor for LINE

Transfer, Export, Backup, Restore LINE Data with Ease

LINE Data Transfer Tool

Umate Pro  - Secure iOS Data Eraser

Permanently Erase iPhone/iPad/iPod Data to Secure your privacy

iPhone Data Eraser

AllDrive  - Multiple Cloud Storage Manager NEW

Manage All Cloud Drive Accounts in One Place

Multiple Cloud Storage Manager

AnyTo  - Location Changer HOT

Best iOS/Android Location Changer

iOS/Android Location Changer

MirrorTo  - Screen Mirror

Professional iOS/Android Screen Mirror

iOS/Android Screen Mirror

LockWiper  for iOS - iPhone Passcode Unlocker HOT

Remove iPhone/iPad/iPod touch Password

iPhone Passcode Unlocker

LockWiper  for Android - Android Passcode Unlocker

Unlock Android FRP Lock & All Screen Locks

Android Passcode Unlocker

Fixppo  for iOS - iPhone System Repair HOT

Fix your iPhone/iPad/iPod touch/Apple TV without losing data

iPhone System Repair Tool

iBypasser  - iCloud Activation Lock Bypasser

Bypass iCloud Activation Lock & Sim Lock

iCloud Activation Lock Bypasser

UltraRepair  - File Repair

Fix damaged and corrupted videos/photos/files/audio

Repair Corrupt Video/Photo/File

Passper  - Password Recovery Expert

Recover password for Excel/Word/PPT/PDF/RAR/ZIP/Windows

Office/Files/Windows Unlocker

FamiGuard  - Reliable Parental Control App

Remotely Monitor Your Kid's Device and Activity

Reliable Parental Control App

VoxNote  - AI Vocie Notes NEW

Transcribe real-time & recorded audio into text

Voice Notes with AI Summaries

Novi AI  - AI Story-to-Video Generator NEW

Convert Your Story & Novel to Original Video Easily

Convert Story & Novel to Video

RewriteX  - Al rewriter for anywhere NEW

Make Your Words Mistake-free And Clear

Mistake-free And Clear

EasifyAI  - Life-Changing AI Toolkit NEW

Innovative AI Toolbox to Take All The Heavy Lifting Off

Innovative AI Toolbox

LokShorts  - Locate best drama & short film NEW

Online Watching 1-minute Short Drama

Filme  - The Best Video Editor

Video Editor and Fast Slide Show Maker

MagicMic  - Real-Time Voice Changer HOT

Change Your Voice in Real-Time Easily

Real-Time Voice Changer

MarkGo  - Effective Watermark Remover

Manage Your Video & Image Watermark Easily

Effective Watermark Remover

V2Audio  - Video Convertor

Super Video Converter Makes Everything Easier

Super Video Converter

VoxBox - Ai Text-to-Speech Generator

Make Voiceover Easily with Cloning & TTS

Text-to-Speech Voice Generator

AnySmall  - Video Compressor NEW

One Click to Compress Videos While Keeping High Quality

One-click Video Compressor

TopClipper  - Video Downloader

Download Any Videos You Like from Any Platforms

Video Downloader from Any Platforms

TopMediai  - Online AI Tools Collection

Discover how AI Transforms Image & audio editing

Online AI Tools Collection

MagicPic  - AI Background Remover & Changer NEW

Remove and Change your Photo Background with Single Click

AI Background Remover & Changer

MusicAI  - AI Music Generator NEW

All-in-one generator to make AI songs

AI Music Generator

Productivity

ChatArt : Chatbot & AI Writer HOT

Generate AI Painting, Write AI Article and Marketing Copy Easily

Chatbot & AI Writer

ZoeAI  - Chat With Any PDF Document NEW

Start AI Conversation With PDF With One Click

The Best PDF AI Chat Assistant

FigCube  - Free Website Builder

Create A Blog Website Within Minutes

Free Website Builder

PromptWink  - AI Prompt Marketplace NEW

Get 1,000+ quality AI prompts from top prompt engineers

AI Prompt Marketplace

AI Tools : Complete Resource of AI Tools

Find The Exact AI Tool For Your Needs

AI Tools Navigation Page

iMyWork  - Personal AI Work Assistant App NEW

Provide professional work advise and support

AI Work Assistant

QRShow  - Professional QR Code Generator NEW

Create various static and dynamic QR codes for free

QR Code Generator

Support Center

Licenses, FAQs

Pre-Sales Inquiry, etc.

How-to Guides

Mobile/PC Issues Solutions

Product Center

iMyFone Products Free Trial

[2024] Best 5 Solutions to Data Source Reference is Not Valid

• 4 Ways: How to Repair Corrupted WMV Files?
• [2024] How to Play MKV Files on Windows & Mac?
• [7 Ways] How to Fix Choppy Video Playback Windows & Mac?
• [7 Fixes] Black Screen While Watching Video on PC
• 5 Methods to Fix 0xc00d3e8c Error in Windows 10/11

When constructing pivot tables in Microsoft Excel , some users have encountered the Data source reference is not valid problem. When you experience this problem, Excel will be unable to generate a pivot table because it is unable to locate the source from which to extract the data needed to do so.

Nevertheless, depending on the situation you're facing, you can usually find a solution. Here, we've included a list of potential causes for this problem along with workable fixes you can try.

Part 1: What is "Data Source Reference is Not Valid"?

Part 2: the reasons of “data source reference is not valid” error in excel, part 3: how to solve ''data source reference is not valid” error.

• 2. Ensure the Reference for the Named Range is Valid
• 3. Ensure that Range Exists & It’s Defined
• 4. Save the File on the Local Disk
• 5. Remove Brackets from the File Name

What should you do and how do you fix the issue if you get the Excel error "Data Source Reference Is Not Valid"? Let's investigate the Data Source Reference Is Not Valid Excel Error problem first.

The error, as its name implies, happens when Excel attempts to refer to a location that is either unavailable, invalid, or nonexistent. Now, there are a number of causes for this problem, but it frequently happens when building a pivot table from a range. The Data Source Reference Is Not Valid Excel Error relates to a location that is no longer accessible, invalid, or nonexistent in Excel.

Let's now look out the reasons for the Data Source Reference Is Not Valid Excel and how to fix it.

There are actually several factors that allow you to experience this problem, not just one.

Let's look at it:

1 Excel File Not Saved on the Local Drive

It's likely that the pivot table is not stored on your local drive. This issue could appear if you access a pivot table directly from a website or other online source. Because your file is in a temporary folder without all the rights that other folders have, it does not have the necessary access.

2 The Pivot Table Data Refers to a Non-existent Range

The specific error was produced if the range was improperly defined. The similar error occurs when you attempt to construct a pivot table with an unknown range. Before attempting to create the pivot table, establish the range in order to correct the problem.

3 The Data Source Refers to a Named Range That Contains Invalid References

When you remove or add rows to named ranges in Excel, the named ranges are automatically adjusted. The named range, however, loses its reference if the entire range is deleted.

All the formulas, validations and conditional formatting depending on that range will not work. And if you try to copy that sheet or file you may get a "reference is not valid" error.

4 Excel File Name Contains Square Brackets

Some of the standard characters cannot be used in pivot tables. The existence of "['or']" is one of the main causes of the Excel error "Data source reference is not valid."

You should follow the following solutions to resolve the Excel data source reference is invalid error.

1 Use the Repair Tool - iMyFone UltraRepair

You must use a repair tool to resolve the "Data Source Reference is Not Valid" error. iMyFone UltraRepair is one of the greatest file repair tools you can use to fix corrupted files.

It is a powerful tool that functions flawlessly. Any file that is broken or corrupt can be fixed by it. The file may be fixed in three simple steps using iMyFone UltraRepair.

• A quick and efficient software that works fast
• All main Excel file formats are supported, as well as other Microsoft Office files such as Word, PowerPoint, and others.
• It only takes one click to start file repair.
• Support both Win & Mac version.

Step 1. Download and open iMyFone UltraRepair on your PC. Then navigate to the "File Repair" area on the left side of the software and click the "Add Files" option. It will launch a browser window and show where the corrupted file was stored. You can load numerous word documents into iMyFone Ultra Repair.

Step 2. The software will import Word files from your PC and show the process on the screen. If you accidentally uploaded the incorrect file, you may remove it by hitting the trash can symbol next to its data.

Step 3. The software will scan the loaded document and correct any large or small errors. You can also stop the repair at any moment. When the file repair operation is finished, a pop-up notification will display that will alert you of the repair results. A file that has been successfully fixed will have a "Success" indicator beneath it.

And it’s done.

• Full Guide to Fix Unexposed Photosk
• Video File Cannot Be Played? Methods to Fix Different Video Error Code
• Troubleshooting the Error Code 224003 in Video Playback

2 Ensure the Reference for the Named Range is Valid

If you defined the name range successfully but are still receiving an error signal, it's conceivable that the named range refers to some cells with invalid values.

Follow the instructions below to solve this issue:

• Open Name Manager by selecting the Formulas tab.
• Now verify that the range refers to the cells that have been set aside for pivot table analysis.
• If you notice any variations, use the Refer to box to switch to the right value.

After making all the necessary modifications, try creating a pivot table once more to see if the Data source reference is not valid Excel problem still occurs.

3 Ensure that Range Exists and It’s Defined

You can get the error data source reference is not valid if you attempt to insert a pivot table into an undefined or non-existent range. Therefore, you must ensure that the range is defined and that it exists in order to resolve this problem.

To find the ideal solution, follow the directions below:

• To proceed, click the Name Manager button after selecting the Formulas tab from the ribbon bar.
• Click New in the Name Manager window and give the range you're going to create a name.
• The cells you want to utilize for the range can then be set using the Refers to box.
• Use the built-in selector or type it out yourself.

The range is then established. Without running across the problem "data source reference is not valid," you can properly create the pivot table.

4 Save the File on the Local Disk

You could get into trouble if you open a file directly from a website or an email attachment. While doing this, you will be in read-only mode. You must save the file to your system before editing it. It might be a possible cause of this mistake.

• Select the Save As option under File.
• By selecting Save As, you can save the file to your local disc.
• Your file will be free of errors once it has been saved to your local drive.

If the Data source reference isn't valid Excel problem still shows, you can check the pivot table to confirm.

5 Remove Brackets from the File Name

Make sure the file doesn't contain the banned characters while building a pivot table. The solution is to rename the.xlsx file's name to eliminate the unnecessary characters.

Take these actions to solve the problem:

• Close the Excel window where the file is presently open.
• Find the Excel file using File Explorer now, and when you get there, right-click on it and choose Rename.
• Remove the brackets from the file name as the pivot table does not allow brackets.

Recreate the pivot table after that to see if the error with the data source reference is fixed.

This is all about the Excel data source reference is not valid error, then. I did my best to put together the effective solutions for the problem.

Utilize the suggested solutions one at a time to eliminate the error and begin building a pivot table without any errors. I hope this article was able to answer all of your questions. iMyFone UltraRepair is a good choice

Melvin Cooper

(Click to rate this post)

Generally rated 5 ( 256 participated)

Rated successfully!

You have already rated this article, please do not repeat scoring!

More Resources

• Unlock iPhone
• Unlock Android
• Repair Files
• iPhone Issues

Home > Repair Files > [2024] Best 5 Solutions to Data Source Reference is Not Valid