How to create a PivotTable with multiple data sources using VBA?

Creating a PivotTable with multiple data sources using VBA can get a bit complex because traditional PivotTables in Excel directly from a single data source. However, you can use the Data Model feature in Excel to combine multiple tables, and then use Power Pivot to create a PivotTable from these. Below is a step-by-step guide on how you can achieve this using VBA, including combining multiple data sources.

Steps to Create a PivotTable with Multiple Data Sources Using VBA

Prepare your Data Sources:

Ensure your data is organized in tables or named ranges in Excel. Having your data in tables makes it easier to manage and reference.

Enable Power Pivot:

Ensure the Power Pivot add-in is enabled. You can do this by going to `File` > `Options` > `Add-Ins`. In the Manage box, choose `COM Add-ins`, and then click `Go`. Check `Microsoft Power Pivot for Excel`, and then click `OK`.

Load Data into the Data Model:

Use the following VBA code to load your tables into the Data Model. This example assumes you have two tables, `Table1` and `Table2`.

   Sub LoadDataIntoDataModel()
       Dim wb As Workbook
       Dim ws1 As Worksheet
       Dim ws2 As Worksheet
       
       Set wb = ThisWorkbook
       Set ws1 = wb.Worksheets("Sheet1")
       Set ws2 = wb.Worksheets("Sheet2")
       
       ' Add Table1 to Data Model
       With ws1.ListObjects("Table1").Range
           wb.Connections.Add2 "WorksheetConnection_Table1", "Connection to Table1", _
               ThisWorkbook.Path & "" & ThisWorkbook.Name

facebooklinkedin