VBA that dynamically updates PivotCache, PivotTables, from external workbook

Hello All,

First, I must admit defeat. While it may be temporary I was hoping that this forum could possible point me in the right direction.

I currently am working with two files.

The first file is a template that acts as a pseudo BI tool.

The second file is a data source that drives the information of the template.

I have created a Macro that makes the opens both files writes their filepaths to the "Intro" worksheet.
The second worksheet then activates and creates a dynamic range to be used in the PivotCache which hopefully will drive the PivotTables. Every PivotTable runs from the same PivotCache.

The issue I am having is twofold.

First it appears that the PivotCache is not being created so there are most definitely flaws in my coding. Second the code runs but it returns either an Error 13 Type Mismatch or when I change the ptRange to ptRange.Address it gives me this long convoluted string that essentially says I don't have my data set up correctly. (Run-Time error -2147024809 (80070057)

I have included my code for any insight. Thank you very much.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added them for you this time. Please read our rules and comply with them in the future.)