VBA to Create Pivot Table in New Worksheet

Xandor

New Member
Joined
Nov 15, 2021
Messages
1
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
I am trying to write a VBA program that will create a pivot table on a new worksheet. For the current code I have, I have a Run-time error '438'. Upon debugging it seems I am having an issue getting the source data to be accurately identified. If anyone would have some helpful advice please share.
My code is pasted below:


Sub createPivotTableNewSheet()

'declare variables to hold row and column numbers that define source data cell range
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long

'declare variables to hold source and destination cell range address
Dim mySourceData As String
Dim myDestinationRange As String

'declare object variables to hold references to source and destination worksheets, and new Pivot Table
Dim mySourceWorksheet As Worksheet
Dim myDestinationWorksheet As Worksheet
Dim myPivotTable As PivotTable

'identify source and destination worksheets. Add destination worksheet
With ThisWorkbook
Set mySourceWorksheet = .Worksheets("Data")
Set myDestinationWorksheet = .Worksheets.Add
End With

'obtain address of destination cell range
myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)

'identify row and column numbers that define source data cell range
myFirstRow = 1
myLastRow = 1000
myFirstColumn = 1
myLastColumn = 4

'obtain address of source data cell range
With mySourceWorksheet.Cells
mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
End With

'create Pivot Table cache and create Pivot Table report based on that cache
Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNewSheet")

'BENS ATTEMPT AT TABLE LAYOUT
With myPivotTable.PivotFields("Type")
.Orientation = xlPageField
.Position = 1
End With
With myPivotTable.PivotField("Width")
.Orientation = xlColumnField
.Position = 1
End With
With myPivotTable.PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With myPivotTable.PivotFields("Length")
.Orientation = xlDataField
.Position = 1
End With

End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,926
Office Version
  1. 365
Platform
  1. Windows
I ran your code and it seemed to work fine, other than an un-related error where you're setting the properties for the Width pivot field. There, it should be . . .

Code:
With myPivotTable.PivotFields("Width")

Does it help if instead of defining a fixed range for the source data you define a dynamic one like this . . .

VBA Code:
mySourceData = mySourceWorksheet.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

???

In any case, your macro can be re-written as follows...

VBA Code:
Option Explicit

Sub createPivotTableNewSheet()

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotCache As PivotCache
    Dim myPivotTable As PivotTable
 
    'declare variables to hold source and destination range
    Dim mySourceRange As Range
    Dim myDestinationRange As Range
 
    'identify source and destination worksheets. Add destination worksheet
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets.Add
    End With
 
    'obtain address of destination cell range
    Set myDestinationRange = myDestinationWorksheet.Range("A5")
 
    'obtain address of source data cell range
    Set mySourceRange = mySourceWorksheet.Range("A1").CurrentRegion
 
    'create the pivot cache
    Set myPivotCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceRange)
 
    'create the pivot table
    Set myPivotTable = myDestinationWorksheet.PivotTables.Add(PivotCache:=myPivotCache, TableDestination:=myDestinationRange, TableName:="PivotTableNewSheet")
 
    'table layout
    With myPivotTable
        With .PivotFields("Type")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("Width")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Date")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Length")
            .Orientation = xlDataField
            .Position = 1
        End With
    End With

End Sub

Notice that among some of the changes the creation of a pivot cache and table have been split up into two different steps.

Does this help?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top