On the Analyze tab, in the Data group, click Change Data Source. If you change the size of your data set by adding or deleting rows/columns, you need to update the source data for the pivot table. I’m trying to implement your VBA code into my workbook but there is a problem. Click on “Ok,” and the pivot table will show the updated data range result. The macro run successfully but never changed the data source. Pivot_Sheet.PivotTables(PivotName). Q: How to make “Set Data_Sheet = ThisWorkbook.Worksheets(“PivotTableData3″)” more flexible by not mentioning the name of the worksheet? To do that, you need to update the Pivot table options. Thank you for the above code. Assuming the correct data range has been selected, you can now click OK to update your Pivot Table. Line14: Change the name of pivot table sheet. **Waiting your reply since last year Oct. https://excelchamps.com/blog/auto-update-pivot-table-range/. Yeah! Which method do you really like? I just need to hide my data source sheet for last 4 columns (Col AQ to AT) before running through the macro to change data source. Now, you have a dynamic range to create a pivot table.All you have to do is just create a pivot table with your source data and after that change the source with the named range using the same method which I have used in the first method of tables.Once you add new data in your source sheet, just refresh your pivot table. When I run the code I get a Run-time error ‘1004’: Method “PivotTables” of object’_Worksheet’ failed. But what is it used for? For more information, see the section "Can't update the data source connection" in Upgrade Power Pivot Data Models to Excel 2013. But how can I change the above code to only use the actual UsedRange? Thanks. LastCol = StartPoint.End(xlToRight).Column. Click any cell inside the pivot table. In the above part of the code, we have specified the variables for the pivot table and source data worksheet. Purpose: Trying to hide some information from Pivot Table Field. I am using a name “SourceData”. When I insert a slicer choosing the date field it shows dates way beyond my data. Good day. Instead create a Dynamic Named Range for the Source Data used in each of your Pivot Table. Thanks a lot Puneet. Have a look at the following links which will help you to create dynamic named ranges. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange). Line13: Change the name of source worksheet. NOTE: pivot tables are one of the INTERMEDIATE EXCEL SKILLS and updating a pivot table range automatically is one of the ADVANCED PIVOT TABLE SKILLS. “Underlying data…..” message was popped up again. _ ChangePivotCache ActiveWorkbook. However all the methods are really good and i personally prefer name range with offset, it doesn’t need any macro and very simple and easy to use. The one problem I am having is that my entire data range is not being selected. It’s fairly easy to do, just select the range and then type the name in the Name Box. I’m getting a Runtime Error 1004 When I get to the Set DataRange Line under ‘Define Start Point & Dynamic Range of Data….Any suggestions? You can change the name of the worksheet from here. Therefore I cannot update my pivot table using any of these methods. If you update the data in your underlying Excel table, you can tell Excel to update the pivot table … _ ChangePivotCache ActiveWorkbook. In the Data group, click the top section of the Change Data Source command. I found the trick for #2. Now, select any of cells from your pivot table and Go to → Analyze → Data → Change Data Source → Change Data Source (Drop Down Menu). The vba code works perfectly. Simply try again to select the correct range OR click Cancel and start again OR press CTRL + Z to undo the change. Using a dynamic method to update pivot table range can save you a lot of time. I tried but it never worked for me. Thanks Puneet. Data sheet is just next worksheet of Pivot Table in closed workbooks. Under the “Analyze” tab, click on the “Change Data Source” option. It works 100%. What This Does. Btw, is it possible to have Pivot Auto Refresh once Workbook is opened? In the Data group, click on Change Data Source button. A few days back I asked with John Michaloudis about his million dollar pivot table advice. it says subscript out of range. Upon debugging it is pointing the code in the Change Pivot Table Data Source Range Address. Go to the Analyze tab in the ribbon. You will get a pop-up window to re-select your data source or you can also enter the name of the table into the range input. The codes you shared would help if there is a way not to specify the exact name of data sheet in vba code and it will not ask user to refresh pivot table every time when open the file to filter the pivot table. It is volatile, use INDEX instead: =$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1)) $A$2 is the upper, left corner of your range. The second one, SourceData… the offset formula is destroyed by the data-import/delete-empty-rows function i have (it pulls 50+ worksheets from external sources, pastes them in to 50+ sheets then concatenates those 50+ sheets into one master sheet… then removes blank rows). Check out the format of dates you have in the ColB. But the vba coding is added into “Worksheet” instead of “ThisWorkbook” or “Module”? Line13: Change the name of source worksheet. The names of my sheets are : SourceTab4 and Pivot4, hi bro iam srinivasan iam an commerce student …. Every time you run this macro it will create a new dynamic range. Home ➜ Pivot Table ➜ How to Automatically Update a Pivot Table Range in Excel. of rows) that should also be factored in while writing the code, ‘———————————————————————————– ‘Set Pivot Table & Source Worksheet ‘———————————————————————————– Set objExcel = CreateObject(“Excel.Application”) Set Data_Sheet = objExcel.Workbooks.Open(“D:\Users\703220609\Documents\rtest\test\test_pvt.xlsx”) Set Pivot_Sheet = Data_Sheet.Worksheets(“Sheet2”), ‘———————————————————————————– ‘Enter in Pivot Table Name ‘———————————————————————————– PivotName = “PivotTable1”, ‘———————————————————————————– ‘Defining Staring Point & Dynamic Range ‘———————————————————————————– Data_Sheet.Activate Set StartPoint = Data_Sheet.Range(“A1”) LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & “!” & DataRange.Address(ReferenceStyle:=xlR1C1), ‘———————————————————————————– ‘Change Pivot Table Data Source Range Address ‘———————————————————————————– Pivot_Sheet.PivotTables(PivotName). Please follow these steps. Thus, I need a vba code to change the data source of all Pivot Tables that copied to closed workbooks. Click on any Cell in the Pivot Table and this will bring up “Analyze” and “Design” Tabs in the top menu bar. This helps soooo much!!!! In the Data group, click on Change Data Source button and select "Change Data Source" from the popup menu. See screenshot: 2. In fact, this is not my original approach. The pivot cache contains the old source data until the pivot table is not manually refreshed or source data range is manually changed. Click any value in the pivot table, then click Change Data Source under the Options tab. Many thanks!!! Please follow the below steps to update pivot table range. You can set the pivot table to update when the Excel file opens, but that doesn’t help if you’re making changes after the file opens.If you can use macros in your Excel file, you can use an event procedure to force the pivot table to refresh automatically if the source data changes. To view the data range reference in the pivot table sheet, select any one of the cells of the pivot table; it will open up two more tabs in the ribbon as “Analyze & Design,” respectively. Groups Dates in a Pivot Table by Month. Thanks. Go to the Data tab in the new window that opens. need more words from you. i have no knowlege on about vba code in excel kindly provide me some guidance on learning vba in excel… my email id email@example.com. The HR report has unique employee ID’s I can use in both. Click any cell inside Pivot table, click Options/Analyze tab, click Options > Options in the Pivot table group 2. Don’t use OFFSET! Enter the data that you want to add to your pivot table directly next to or … On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab). It will automatically exclude the last 4 columns and invisible from PivotTable Fields. Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long Dim ws As Worksheet: Set ws = ActiveWorkbook.Worksheets(“PivotTable”) ”CC: Newly added Dim wsNext As Worksheet ”CC: Newly added Set wsNext = ws.Next ”CC: Newly added, ”CC:Not using because every file will have different datasource name –> ”Set Data_Sheet = ThisWorkbook.Worksheets(“Surname,FirstName”), ”CC:Not using ThisWorkbook because I cannot copy the code into new file 1 by 1 –> ”Set Pivot_Sheet = ThisWorkbook.Worksheets(“PivotTable”), ‘Set Pivot Table & Source Worksheet Set Pivot_Sheet = ActiveWorkbook.Worksheets(“PivotTable”) ”CC:Tweaked to ActiveWorkbook so that I can run from other workbook. Sir, Why table is better than dynamic Named Range? End If 'Change Pivot Table Data Source Range Address Pivot_sht.PivotTables(PivotName).ChangePivotCache _ ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_sht.PivotTables(PivotName).RefreshTable End Function Good day. How do I fix this? Set Data_Sheet = ThisWorkbook.Worksheets(“PivotTableData3”). Which means I get “blanks” in pivot table. I did my best to work for it, but I failed. I have the same issue. (Note: The source data also keeps changing (increasing / decreasing no. You will get a pop-up window with your current data range. Thanks for the showing other options too. Thanks for sharing this. So here is the code to use to update pivot table range with VBA. The table approach is NOT working when the number of rows for current iteration is greater than the number of rows in the template file.. There is another simple trick. The other best way to update pivot table range automatically is to use a dynamic range. If you use pivot tables in your work frequently... Every time when you add new data in the source sheet you need to update the source range for the pivot table... ...if you add data to your source sheet every day you have to update source range every day. Things you have to change before you use it in your workbook. When the Change PivotTable Data Source window appears, change the Table/Range value to reflect the new data source for your pivot table. You don’t have to change source data again and again. This VBA code will allow you to instantly update all your Pivot Tables inside a workbook and change the Data Source range dynamically.This macro is perfect for instances where you have an ever-changing data set that is feeding-in a bunch of Pivot Tables.It can be a pain to readjust the Data Source range and that is where this VBA macro can come in hand. But, I still looking for solution on #1. We can change the data source to expand the data range. In that master workbook (A), I have a list of table to list out filenames saved in specific path. Any suggestions? If you still have a problem to use this code, please write me in the comment box. This doesn’t mean, fortunately, that you need to go to the work of re-creating your pivot table. Line17: Change the name of the pivot table. After this refresh your PivotTable. For e.g., you can expand the source data to include more number of rows of data. I don’t know if the problem comes from the Pivot Table Name or not because I gave nowhere a name of the pivot table, so I put a random name “Something” into the parenthesis. Thanks for the code. Really appreciate your guidance. So source data file is automatically updated. From now on-wards every time when you add new data into your source sheet it will increase pivot table range to automatically update it. Subsequently, click on a pivot table linked to the raw data, then go to PivotTable Tools>Analyze>Data>Change Data Source. Set Data_Sheet = ws.Next, ‘Enter in Pivot Table Name PivotName = “PTSample”, ‘Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range(“A7”) LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)), NewRange = Data_Sheet.Name & “!” & DataRange.Address(ReferenceStyle:=xlR1C1), ‘Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). Once the named range have been created, you have to just replace the source data range in each of your pivot table once with the named range. Every time when you add data below the table the range is updated automatically because you have selected the entire column. When done properly, the Table/Range windows should show the table name (you can also type it in yourself). If you get it wrong, and the wrong data range has been selected, don't panic! Use the Refresh Data command to update the report”. You can play around with the INDEX range and the two COUNTA ranges to adjust to your needs. I need to update my employee training tracker with a monthly HR employee report. If the data source sheet is always the 2nd worksheet next to Pivot Table. Line14: Change the name of pivot table sheet. Once you click on name manager you will get a pop-up window. Although the dates are not highlighted because my data doesn’t go that far, it bothers me that the scroll bar is active for unused dates. My intention is to remove the old data source of copied Pivot in closed workbooks and let the Pivot Table (always 1st sheet) to get the new data source in closed workbooks (always 2nd sheet) through the macro set in master file. When the Change PivotTable Data Source window appears, change the Table/Range value to the new data source that you want for your pivot table and then click on the OK button. You can use the Name Box to select the named range as well. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows have been added. I would like to be able to automatically update the report each month and identify new employees and employees no longer with the company. Slightly tweaked but basically followed yours. Now, whenever you add new data into you datasheet it will automatically update pivot table range and you just have to refresh your pivot table. Share your views with me in the comment section, I’d love to hear from you. After you change the data range, click the relative pivot table, and click Option (in Excel 2013, click ANALYZE) > Change Data Source. Manually refresh Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. The source data will contain the whole data. Just choose one of the above-mentioned methods, put your data on your data sheet and refresh your pivot table.If you ask me I always use tables.Now, tell me one thing. Launch Microsoft Excel. And every time changing pivot table range, is a mess. But, I still have new obstacles for below and hope you can help on this. Now this will take us to … ” & PivotName & ” is updated now.”. These few lines can dynamically update any pivot table by changing the source data range. Download this sample file from here to learn more. ...so without any further ado, let's get started. Edit table and column mappings (bindings) When you change a data source, the columns in the tables in your model and those in the source may no longer have the same names, though they contain similar data. _ ChangePivotCache ActiveWorkbook. In above formula, I have used offset function to create a dynamic range.I have mentioned cell A1 as the starting point and then without mentioning rows and columns, I have specified the height and width of the range by using COUNTA Function.COUNTA function will count the cells with values from column A and row 1 and tell offset to expand it’s height and width accordingly.The only thing you have to take care that there should be no blank cell in between your column A and row 1. Your email address will not be published. Specify the scope of the range. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain. Believe me, it's a million dollar advice. The copied Pivot Table still linked to the original data source in old workbook (master file). Above part of the code will refresh the pivot table and show a message to the user that pivot table in updated now. When you update a pivot table’s source data in Excel, the pivot table does not update automatically. You can automatically refresh the Pivot table when you open the file in Excel. Click on the PivotTable. Now when you return to your pivot table, … If you already have a pivot table in your worksheet.. ...you can use following steps to convert your data source into a table. Follow these steps, to find the source data for a pivot table: Select any cell in the pivot table. Change Data Source. Figure 7. Thanks for your sharing. Can a macro code me written to refresh all these pivots using the same data source? The macro adds a sheet to your active workbook, showing a list of the file's named ranges. Therefore, I am looking for alternative solution to help me and my colleague to improve our productivity as we need to change data source for maybe 250 files within few hours. After you change the data range, click the relative pivot table, and click Option (in Excel 2013, click ANALYZE) > Change Data Source. Add a comment to describe your named range. (2) Possible to mark specifc column as last column? There are a variety of reasons you might need to update the pivot table. Naming a range is relatively easy and when you use the name rather than the reference in a formula it really aids the understanding of the formula. Open the Google Sheet with the pivot table. Why? Most of the people love to use VBA codes. A lot of complicated solutions… This one, really simple, works perfectly! Tip: To update all PivotTables in your workbook at once, click Analyze > Refresh All. Looked for a lot of solutions for refreshing the data source for pivots. In the following guide, we explore how to update the Pivot Table after the source data changes. One Question, what changes should I make to the code so that a second or third pivot table that’s linked to the same data source is also updated/refreshed? This will open the 'Change PivotTable Data Source' windows, where you can select the data area of your table. trying to do it through a pivot table. In above part of the code, enter the name of the pivot table on which you want to use this code. Then throw the below code in that workbook so you can use the power of VBA to automatically adjust the Source Data for your Pivot Table. A name for your new range. Above part of the code will create a dynamic range by using cell A1 from the data source worksheet. Use this simple line of code to update your pivot table automatically. Check the box that says, “Refresh data when opening the file.” Thank you so much. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab). Click on the named range that you want to use, and click OK; ... Excel Pivot Table Data Source – with sample code to update all the pivot tables. Thank you, this is what I was looking for! Following are the steps to create a dynamic range. Let say, the last column is AT but I would like the last column of data source stop at column AP. Puneet, the VBA code works great, thanks! I might have 200 files with different name of worksheet or different name of sheet name required to refresh Pivot data source, so it might not efficient if i need to modify 1 by 1. Then in the pop-up dialog, select the new data range you need to update. So here is the … I tweaked the codes and it seems like work for me now. The pivot table in Excel is one of the most vital and versatile tools available. In the beginning of your code, you wrote: Dim lastRow As Long. “The PivotTable report was saved without underlying data. However, I get a syntax error message when trying to run and it points to this particular line. After changing the names of the object in line 13,14 and 17 with, ‘Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets(“SourceTab4”) Set Pivot_Sheet = ThisWorkbook.Worksheets(“Pivot4”), ‘Enter in Pivot Table Name PivotName = “Something”. How can I fix this? You can specify between current worksheet or workbook. Whats people lookup in this blog: How To Update Pivot Table Range; How To Update Pivot Table Range In Excel 365 Try setting your retained data in the Pivottable to ‘None’: In your PivotTable Options, click the Data tab and then select ‘None’ from the Number of items to retain per field drop-down list. You can use either the desktop icon, the Programs listed in the Start … Select any of the cells in your data source. No one is changing the data in the source data file. I tried again and this round it seems like help me a lot. I noticed there will be a message pop up when I want to filter the pivot table after opened the file again. Now, let me show you how this code works so that you can easily modify it as per your need. Below are the steps you need to follow to group dates in … The VBA code worked perfect. 2. my source data is constantly being deleted / rewritten. Click Analyze > Refresh, or press Alt+F5. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange), ‘———————————————————————————– ‘Ensure Pivot Table is Refreshed ‘———————————————————————————– Pivot_Sheet.PivotTables(PivotName).RefreshTable, ‘———————————————————————————– ‘Complete Message ‘———————————————————————————–, Pivot_Sheet.Activate MsgBox “Your Pivot Table is now updated.”, I am calling the VBscript via R. The error I am getting : D:\Users\703220609\Documents\R_Code\script.vbs(21, 69) Microsoft VBScript compilation error: Expected ‘)’. Now delete the existing data range and choose the new data range. Forgive me if I asking some funny questions. Choose the Options button. thanks a lot for sharing wonderful tricks to make excel easy. These columns cannot delete from data source. In this post, I’d like to share with you 3 different methods... ...which you can use to update the source range of a pivot table automatically. Hi Puneet, I am facing Invlaid procedure call or argument error, How To Link Slicer to Multiple Pivot Tables in Excel, How to Apply Conditional Formatting to a Pivot Table, How to Group Dates in a Pivot Table in Excel, How to Create a Pivot Table from Multiple Worksheets, Pivot Table Tips & Tricks for Advanced Excel Users. How to Auto Refresh Pivot Tables Using VBA : To automatically refresh your pivot tables you can use VBA events. Any recommendation? All Rights Reserved, How to Add or Remove Subtotals in Pivot Table, How to Check Computer Specs in Windows 10, How to Remove Bloatware From Windows 10 PC, How to Setup Chromecast on Android and iPhone, Mac vs PC Comparison: Should You Buy Mac or PC, Kindle E-Reader Comparison | Which Kindle to Buy. Any “way” to change this code more flexible without a need to specify the exact worksheet name? Hi When i run this code, i got “Invalid Procedure Call or Arguments” Error Occur. Used to be my question and now I found solution by tweaked the codes. Click on the OK button. I believe my codes were not perfectly to instruct pivot table to use the worksheet next to it as data sheet. You can use either of 3 methods of auto refreshing pivot tables. As well as collating data in a pivot table to display data in a usable way, you can also use charts as a graphic demonstration of your findings. And, please don’t forget to share this tips with your friends. I can only see it once in the code, and that’s where you named it. This code is giving me a run-time error. Did anyone reply directly to you? Ms excel 2010 how to change data source for a pivot table change the source data for a pivottable excel how to update pivot table range in excel ms excel 2010 how to change data source for a pivot table. Select the Analyze tab from the toolbar at the top of the screen. You can base a pivot table on the dynamic range. Hi, when i am tried to run this code, i am getting run time 1004 at, ‘Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(Gangineni).ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange). Click the button beside the Table/Range bar and select cells B2:D14 to expand the data selection. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange), ‘Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable, ‘Complete Message Pivot_Sheet.Activate MsgBox “Task Complete! By applying a table in source data you don’t need to change the source range of your pivot table again and again. We have created references of pivot tables name pt, pivot cache named pc and a range named source_data. Tip: change your data set to a table before you insert a pivot table. Changing the Source Data of a PivotTable. Here’s how to integrate a chart into a pivot table. . The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. 1. However, most of them use the same data source. If your column headings are starting from A1, that is no rows above to it then you can simple put range as A:E or A:Z whatever it is. I used option 1B but if I copy paste smaller range of data in the sheet, it still keeps the old data. Not all of it use the same data source. Sorry for my long message. 1. In your name manager window click on new to create a named range. Thanks a lot. so my source data is constantly changing drastically and none of these methods is working for me yet, use whole columns as source, not just filled rows. After updating the source data, and when you open this file next time, the Pivot Table will be updated automatically. 2. It allows you to look at your data from a wide range of customizable views. Select all columns you want to include in the chart. I like the table method but already Iam using the same. If I clear out the data before copying it, it still keep the Table1 as large as old one. The VBA worked great for my pivot table. Yes that’s right, the more frequently you add data, the more you need to update the source range. As I have “” and hidden formulas lower down that I don’t want part of the UsedRange. That’s also great but it will un-necessarily show the blank rows and will also not work if below your data you have another data which you don’t want in your pivot. Add or change your data. How to avoid this without need refreshing the Pivot Table? Puneet is using Excel since his college days. Most of the people love to use VBA codes. This is because my data tab has a blank line in between every data line so the data really is only picking up the first two lines. Create a template with your Pivot Table already made and just replace the data every time you need to update. Advantages of using a dynamic range as a data source: A dynamic range will automatically expand or contract, if data is added or removed. Then, the macro prompts you to enter one of those names, as the new data source for all the pivot tables. Use shortcut key Control + T or Go to → Insert Tab → Tables → Table. Update Pivot Table using a VBA Code. Select any cell in the pivot table. Once I run through the coding, it seems like this option is being removed. However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable. when I launch the macro, there is an error window that pop up: Method ‘PivotTables’ of object’_Worksheet’ failed: And in the debugger this is selected in yellow: Pivot_Sheet.PivotTables(PivotName). (1) My original PivotTable was originally tick with PivotTable Options > Data to “Refresh data when opening the file”. so I still haven’t found one of these to work. Any way to solve it or retain my PivotTable Options after run through the macro? Earlier on, I referenced to other websites and managed to create vba code to copy my “default” format of Pivot Table in master workbook (A) to multiple closed workbooks as mentioned above. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. Press enter and the name is defined. Whenever you add new data, it will automatically update pivot table range. Every time before creating a pivot table make sure to apply table to source data by using following steps. **My PivotTable Name and name of pivot table worksheet is never changed. I don’t want to use xlDown but rather the actual UsedRange….Any Suggestions please? Hi, I have multiple pivots in my workbook. My new codes. VBA or table? best thing is to create a table (well, a table is a solution in itself) then use that table name in the source name. Do you have any good sample of vba code to change the data source of a Pivot Table that being copied from 1 master file to multiple closed workbooks? Add a chart to a pivot table in Google Sheets. _ ChangePivotCache ActiveWorkbook. Is there any way to refresh the pivot table without refreshing every time. Dynamic range can expand automatically whenever you add new data into your source sheet. Now to create a pivot table select any cell of your data. Please follow the below steps to update pivot table range. My data source is a table that automatically updated through the paste link command. Thank you a lot!! I am trying hard to create vba code in another master workbook (A). The Table approach works perfectly when we do this manually.. but say we have a template excel file and we have one data sheet and one pivot sheet.. and each time we delete all the rows (except the header one) from the template excel file and insert rows to this data sheet programmatically (with apache POI)..