Pivot table in same sheet VBA

Question

I am trying to add a pivot table to the same worksheet I am on (the sheet is called holders (corp)) but am having trouble with that.

Sub PivotTable()

Sheets("Sheet2").Select

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim pf As PivotField
Dim StartPvt As String
Dim SrcData As String
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).row

'Determine the data range you want to pivot
 SrcData = ActiveSheet.Name & "!" & Range(Cells(1, "A"), Cells(LastRow,"E")).Address(ReferenceStyle:=xlR1C1)

'Create a new worksheet
 Set sht = Sheets("HOLDERS (CORP)")

'Where do you want Pivot Table to start?

StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="HolderssPivotTable")
End Sub

I get a debug issue related to the call procedure at the very last 3 lines of code but am not sure why. Help would be much appreciated!

I receive a message saying invalid call procedure or something of that sort! I'll post it — markos
– markos, Commented Jul 19, 2016 at 18:41

Scott Holtzman · Accepted Answer · 2016-07-19 19:14:58Z

1

Excel VBA cannot accept R1C1 cell addresses in Range objects.

Change

StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)

To

StartPvt = sht.Range("A1").Address

Then change

Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="HolderssPivotTable")
End Sub

To

Set pvt = pvtCache.CreatePivotTable(TableDestination:=sht.Range(StartPvt), _
    TableName:="HolderssPivotTable")

Update - Full refactored code to ensure pivot table ends up on Holders (CORP) sheet.

Sub PivotTable()

Dim sht2 As Worksheet
Set sht2 = Sheets("Sheet2")

With sh2

    Dim LastRow As Long
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    'Determine the data range you want to pivot
    Dim SrcData As String
    SrcData = .Name & "!" & .Range(.Cells(1, "A"), .Cells(LastRow, "E")).Address

End With

Dim sht As Worksheet
'Create a new worksheet
Set sht = Sheets("HOLDERS (CORP)")

'Where do you want Pivot Table to start?
Dim StartPvt As String
StartPvt = sht.Range("A1").Address

'Create Pivot Cache from Source Data
Dim pvtCache As PivotCache
Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Dim pvt As PivotTable
Set pvt = pvtCache.CreatePivotTable(TableDestination:=sht.Range(StartPvt), TableName:="HolderssPivotTable")

End Sub

edited Jul 19, 2016 at 19:14

answered Jul 19, 2016 at 19:02

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

markos Over a year ago

Thanks Scott, but this method still adds the pivot table on a new sheet, is there a way for me to make it add it to the sheet named HOLDERS(CORP)?

Scott Holtzman Over a year ago

@markos - it worked for me when I tested, but I refactored the code to make it cleaner by assigning all ranges and objects and working directly with them.

markos Over a year ago

seems to be working! Thanks a million! How come it now moves it to HOLDER(CORP)? It seems like it still adds a new sheet based on the code. But based on when I run it, a new sheet isn't added but the table is added on my sheet named HOLDERS (CORP)

Scott Holtzman Over a year ago

@markos - this line Set sht = Sheets("HOLDERS (CORP)") does not create a new sheet (i just left your comment there). It sets the variable sht to the existing sheet. Please mark as answered so others benefit.

skkakkar Over a year ago

@Scott Holtzman Typo ~Set sht2 = Sheets("Sheet2") With sh2~ to With sht2

Collectives™ on Stack Overflow

Pivot table in same sheet VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related