VBA to Create Pivot Table in New Worksheet

[Xandor]

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

 

[Domenic]

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 . . .

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 . . .

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

???

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

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?