Rewriting Referenced Data for VBA Pivot Table

[riemerco]

I need help rewriting VBA code for a pivot table.

I create the same pivot table dozens of times per month. My workbook always starts off with pre-existing "Detail" and "Summary" tabs. I then create a pivot table from all of the data on the "Detail" tab and use it to create a pivot table on the "Summary" tab. I need help standardizing the below VBA code so that it will work on any workbook I have open which already has a "Detail" tab and a "Summary" tab.


Sub Create_Pivot_Table()
'
' Create_Pivot_Table Macro
' Creates standard pivot table.
'

'
Cells.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Detail!R1C1:R1048576C39", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Summary!R1:R1048576", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
Sheets("Summary").Select
Cells(1, 1).Select
End Sub

[Paul_Hossler]

something like might be a start




Option Explicit
Sub Create_Pivot_Table_1()
Dim wsDetail As Worksheet, wsSummary As Worksheet

With ActiveWorkbook
On Error Resume Next
Set wsDetail = .Worksheets("Detail")
Set wsSummary = .Worksheets("Summary")
On Error GoTo 0

If wsDetail Is Nothing Or wsSummary Is Nothing Then Exit Sub

.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wsDetail.Cells(1, 1).CurrentRegion.Address, _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=wsSummary.Cells(1, 1).Address, TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
End With

wsSummary.Select
End Sub

[riemerco]

Hey Paul. Thank you for the help.

I'm having trouble with the below argument. I'm quite new to VBA so sorry I can't be more specific.


.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wsDetail.Cells(1, 1).CurrentRegion.Address, _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=wsSummary.Cells(1, 1).Address, TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14


I'm not sure where the problem is exactly, but could use any further assistance offered.

[Paul_Hossler]

Guessing again -- this puts the worksheet names into the Source and Location addresses



.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & wsDetail.Name & "'!" & wsDetail.Cells(1, 1).CurrentRegion.Address, _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="'" & wsSummary.Name & "'!" & wsSummary.Cells(1, 1).Address, TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14