Hack 49 Efficiently Pivot Another Workbook's Data   Use data residing in another workbook as the source for your PivotTable . When creating a PivotTable in Excel, you have lots of options for your data source. By far the easiest and most powerful approach is to use data that resides within the same workbook. Unfortunately, for whatever reason, this is not always possible or feasible . Perhaps the data that resides in another workbook is entered daily, for instance, and the users entering the data should not see the PivotTable. Using a dynamic named range will greatly decrease the refresh time needed for your PivotTable to update. As you cannot reference a dynamic named range from another workbook, this also means you prevented the PivotTable from referencing perhaps thousands of blank rows and causing the file size to increase substantially. This way, you can pull in data from another workbook, and then base your PivotTable on the data in the same workbook rather than referencing it externally. In the workbook that will contain your PivotTable, insert a new worksheet and call it Data . Open the workbook containing the data to be referenced, and ensure that the worksheet containing the data is the active sheet. In any spare cell on this worksheet, enter this formula: =IF(A1="","",A1) where A1 is the very first heading of your data table. Select cell A1. Then cut it, activate your original workbook, and paste cell A1 in cell A1 on the Data sheet. This will give you the reference to the other workbook. Copy this cell across as many columns as there are headings in your data source. Then select Insert  Name Define, and in the Names in Workbook: field, type PivotData . In the Refers to: box, type the following: =OFFSET($A,0,0,COUNTA($A:$A),COUNTA(:)) Click Add, then click OK. Next, to insert some code that will run each time the workbook is opened, right-click the Excel icon (located at the top left corner of the screen) and enter the following code: Private Sub Workbook_Open( ) With Worksheets("Data") .Range("2:1000").Clear .Range("1:1").AutoFill .Range("1:1000") .Range("2:1000") = .Range("2:1000").Value End With End Sub
To return to Excel, close the script window or press Alt/  -Q. The preceding code includes only 1,000 rows of data. This figure should always be greater than the number of rows you believe you will need. In other words, if your table in the other workbook contains 500 rows, add a few hundred more to accommodate any growth in the original table.  | Avoid using an extremely high row number (like 10,000, unless you actually have that much data), as this will greatly impact how quickly the code runs and the data updates. | |
Save the workbook, close it, and then reopen it, making certain that you enabled macros. The code you added will fire automatically and will copy the formulas in row 1 on the Data sheet, then automatically convert all but row 1 into values only. This will leave you with a copy of your original data source, which will update each time you open the workbook. Now you can hide this sheet if you want by selecting Format Sheet Hide or by using the method described in [Hack #5]. Now, to base a PivotTable on this dynamic named range, select anywhere within the PivotTable, then select the Wizard option from the PivotTable toolbar. Click the Back button until you reach Step 1 of the Wizard. Select the first option, Microsoft Excel List or Database, click Next, and in Step 2, type =PivotData (the name of the dynamic named range). Then click Finish. You will not experience the lag that often occurs when a PivotTable is referencing an external data source because now the data itself is stored within the same workbook. As an added bonus, because you can use a dynamic named range, the PivotTable is dynamic without having to reference heaps of blank rows, and the file is kept to a manageable size. | 
