Creating a PivotTable from multiple ranges using VBA involves several steps. You’ll first need to consolidate the multiple ranges into a single data range or use PivotTable tools to combine them if possible. Here’s a step-by-step guide on how you can accomplish this in Excel using VBA:
Step 1: Consolidate the Data
If your data is not already in a single contiguous range, you need to consolidate it into one. This is because PivotTables typically work with single data ranges. You can either:
- Use Excel’s Consolidate feature manually.
- Write a VBA script to combine the ranges programmatically into a new sheet.
Step 2: Create the PivotTable Using VBA
Once your data is in a single range, you can create the PivotTable with VBA. Below is an example script for creating a PivotTable from consolidated data:
Example VBA Script:
Sub CreatePivotTable()
Dim wsData As Worksheet
Dim wsPivot As Worksheet
Dim dataRange As Range
Dim pivotRange As Range
Dim pivotTableCache As PivotCache
Dim pivotTbl As PivotTable
' Set your data worksheet
Set wsData = ThisWorkbook.Sheets("DataSheet") ' Change "DataSheet" to your data sheet's name
' Set the data range
Set dataRange = wsData.Range("A1").CurrentRegion ' Adjust the range as necessary
' Add a new sheet for PivotTable (Optional)
On Error Resume Next
Set wsPivot = ThisWorkbook.Sheets("PivotSheet")
On Error GoTo 0
If wsPivot Is Nothing Then
Set wsPivot = ThisWorkbook.Sheets.Add
wsPivot.Name = "PivotSheet"
End If
wsPivot.Cells.Clear ' Clear previous data
' Define the Pivot Table Range
Set pivotRange = wsPivot.Range("A1")
' Create the Pivot Table Cache
Set pivotTableCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=dataRange)
' Create the Pivot Table
Set pivotTbl = pivotTableCache.CreatePivotTable( _
TableDestination:=pivotRange, _
TableName:="MyPivotTable")
' Add fields to the Pivot Table
' Add your Row, Column, and Data fields here
With pivotTbl
.PivotFields("Field1").Orientation = xlRowField
.PivotFields("Field2").Orientation = xlColumnField
.PivotFields("DataField").Orientation = xlDataField
.PivotFields("DataField").Function = xlSum
End With
MsgBox "PivotTable created!"
End Sub
Key Points:
- wsData: This is the worksheet that contains your data.
- dataRange: Specify the range that includes your consolidated data.
- wsPivot: This is the worksheet where the PivotTable will be created.
- pivotTbl: Customize the row, column, and data fields to match your dataset.
Notes:
- Make sure that the data you’re working with is clean and formatted correctly.
- Adjust range references (`”A1″`, `”DataSheet”`, `”Field1″`, etc.) according to your specific data and requirements.
- Ensure there is no existing sheet with the same name or handle error conditions as done in the script.
This script assumes that the data is already consolidated into one worksheet, which is crucial for creating a PivotTable. If you need to consolidate different ranges across multiple sheets programmatically, additional logic to combine those before creating a PivotTable is necessary.
Unlock Your Potential
Excel
Access
Power BI
