Create pivot table in the same worksheet using excel VBA

[BMD44]

Hello,

I have below code which creates pivot table sheet after the current sheet. Can anyone please suggest how to create pivot table in the same sheet.

Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("SrcData")

Thanks in advance.

 

[Jimmypop]

Hi there

Have you maybe tried the following?

Sub Macro2()
Dim MyRange As Range
Set MyRange = ActiveSheet.Range("G1") 'Your range on the sheet where Pivot Table needs to go. Also remeber to update SourceData Table1 to your table name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
             "Table1", Version:=8).CreatePivotTable TableDestination:=MyRange, _
               TableName:="PivotTable3", DefaultVersion:=8
End Sub

 

[BMD44]

Thank you.

I have tried below and it gives error. Can you please let me know where can I specify source range details here

Sub Button4_Click()
Dim MyRange As Range
Set MyRange = ActiveSheet.Range("B25") 'Your range on the sheet where Pivot Table needs to go. Also remeber to update SourceData Table1 to your table name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
             "JE Detail", Version:=8).CreatePivotTable TableDestination:=MyRange, _
               TableName:="PivotTable3", DefaultVersion:=8
End Sub

 

[Alex Blakenburg]

I have renamed Jimmy's variable to make it a little more obvious:
Note: Jimmy used a Table as his data source and you would be well advised to do the same. That way it will automatically expand if you add more data.

Sub Button4_Click()
Dim rngDestPvt As Range
Set rngDestPvt = ActiveSheet.Range("B25")   'Your range on the sheet where Pivot Table needs to go. Also remeber to update SourceData Table1 to your table name
   
Dim rngSrcData As Range
Set rngSrcData = Range("A1").CurrentRegion  'Your range where the source data is

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSrcData, _
                Version:=8).CreatePivotTable TableDestination:=rngDestPvt , _
                TableName:="PivotTable3", DefaultVersion:=8
End Sub

 

[BMD44]

I have renamed Jimmy's variable to make it a little more obvious:
Note: Jimmy used a Table as his data source and you would be well advised to do the same. That way it will automatically expand if you add more data.

Sub Button4_Click()
Dim rngDestPvt As Range
Set rngDestPvt = ActiveSheet.Range("B25")   'Your range on the sheet where Pivot Table needs to go. Also remeber to update SourceData Table1 to your table name
  
Dim rngSrcData As Range
Set rngSrcData = Range("A1").CurrentRegion  'Your range where the source data is

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSrcData, _
                Version:=8).CreatePivotTable TableDestination:=rngDestPvt , _
                TableName:="PivotTable3", DefaultVersion:=8
End Sub

Thank you. This works.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.
PS: I think @Jimmypop did the hard part

 

[Jimmypop]

Thanks for letting us know. Glad we could help.
PS: I think @Jimmypop did the hard part

Thanks Alex.. Lol was actually quite simple... I just used the macro recorder and then just updated the code generated to declare and set the range... Looking at it now I could have gone deeper and declared / set the source and destination ranges as you did but it was done during a quick lunch break

 

[Alex Blakenburg]

It all helps, it's a team effort.