Ultimate goal is to automatically update the source data for 4 pivot tables that are on 1 worksheet. I would like to be able to drop an Excel workbook (that contains data worksheets and ones with pivots) in this folder and it automatically be updated everyday without have to change the VB code.
The data for those 4 pivot tables are on 4 different worksheets w/in the same workbook. I'm familiar with a dynamic named range, but the 4 worksheets that contain the data are replaced daily via automated Access output which creates an error. Having the pivots reference a named range would work if I could append the data from Access to a worksheet instead of completely overriding it....haven't tested this, but seems logical After receiving an error when I attempted to purge a linked Excel table (a sheet that contained data for the pivot table) and getting an error, I searched the forums and realized it's not possible.
I know how to do this adhoc by matching the pivot table names with their respective worksheets, but there are many other documents with similar layouts where this would beneficial. Right now I have the data imported to Excel from Access and am using a dynamic range for the pivots.
The most terrible thing is that the workbook you need to combine contains multiple worksheets. Close Filename = Dir() Loop End Sub Tip: In the above code, you can change the path to the one that you are using.4.
If you are required to repeat this task very frequently then you should download the Excel Add-In.
Once added to MS Excel it does not required any authentication and Add-In menu options will be available all the time.
Do you have lots of Excel sheets to merge into one?
You can download and use our merge macro or Add-In.