VBA Create Pivot Table - Need Some Modification

Myproblem · Sep 10, 2011

VBA code under works fine: it create (if is not there already) pivot table from sheet PivotTable into new sheet Report , or if it is there it is overwrite it (delete existing sheet Report) and create new one.
The problem occurs then there is no sheet with name Report and you Run named macro, because it create sheet Report but without no data, no pivot table, But second time you click to Run named Macro it works.

Simply, if there is no sheet Report create one with adequate Pivot Table, and if there is already sheet Report Pivot Table overwrite it.
How to do it without clicking, running same macro twice

i was wondering why it is happeninng?

ps original vba code reference go to author of book Pivot Table Data Crunching Excel 2007

 Sub Report()
' Page 206 of Pivot Table Data Crunching
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim pt As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PivotTable")

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Dim oSheet As Worksheet

On Error GoTo errHandler

'creating a new excel worksheet called "xxx"
Set oSheet = Worksheets.Add(After:=Worksheets("PivotTable"))
With oSheet
.Name = "Report"
.Activate
End With
Exit Sub

errHandler:

'if error due to duplicate worksheet detected
If Err.Number = 1004 Then

Worksheets("Report").Delete
Application.DisplayAlerts = True

'rename and activate the new worksheet
With oSheet
.Name = "Report"
End With
Else
'cancel the operation, delete the new worksheet
Application.DisplayAlerts = False
oSheet.Delete
Application.DisplayAlerts = True
End If
' Delete any prior pivot tables
For Each pt In WSD.PivotTables
pt.TableRange2.Clear
Next pt
WSD.Range("DA1:KH1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)

' Create the Pivot Table from the Pivot Cache
Set pt = PTCache.CreatePivotTable(TableDestination:=Worksheets("Report"). _
Cells(2, 2), TableName:="PivotTable1")
' Turn off updating while building the table
pt.ManualUpdate = False

' Set up the row & column fields
pt.AddFields RowFields:=Array("Month", "Product"), _
ColumnFields:="Data"

' Set up the data fields
With pt.PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,###"
End With

End Sub

pedie · Sep 10, 2011

try it again....not sure what you want.
if report sheet if it exist
else
create new one and then your PIVOT

Code:

[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub Report()[/FONT]
[FONT=Courier New]Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim pt As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PivotTable")[/FONT]
[FONT=Courier New]Application.ScreenUpdating = False[/FONT]
[FONT=Courier New]Application.DisplayAlerts = False[/FONT]
[FONT=Courier New]On Error Resume Next
Worksheets("Report").Delete
On Error GoTo 0[/FONT]
[FONT=Courier New]Dim oSheet As Worksheet[/FONT]
[FONT=Courier New]Set oSheet = Worksheets.Add(After:=Worksheets("PivotTable"))
With oSheet
.Name = "Report"
.Activate
End With[/FONT]
[FONT=Courier New]Application.DisplayAlerts = True[/FONT]
[FONT=Courier New]'cancel the operation, delete the new worksheet
Application.DisplayAlerts = False
oSheet.Delete
Application.DisplayAlerts = True
End If
' Delete any prior pivot tables
For Each pt In WSD.PivotTables
pt.TableRange2.Clear
Next pt
WSD.Range("DA1:KH1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)[/FONT]
[FONT=Courier New]' Create the Pivot Table from the Pivot Cache
Set pt = PTCache.CreatePivotTable(TableDestination:=Worksheets("Report"). _
Cells(2, 2), TableName:="PivotTable1")
' Turn off updating while building the table
pt.ManualUpdate = False[/FONT]
[FONT=Courier New]' Set up the row & column fields
pt.AddFields RowFields:=Array("Month", "Product"), _
ColumnFields:="Data"[/FONT]
[FONT=Courier New]' Set up the data fields
With pt.PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,###"
End With
End Sub[/FONT]
[FONT=Courier New]

Myproblem · Sep 10, 2011

thx
tried but it do not work
shortly: vba works fine but why i need to twice run same macro to get pivottable on sheet Report. the problem is I think in the part of code where it create sheet report (if there is no one with that name)

any tips

VBA Create Pivot Table - Need Some Modification

Myproblem

Board Regular

Excel Facts

pedie

Well-known Member

Myproblem

Board Regular

Similar threads

Forum statistics

Share this page

VBA Create Pivot Table - Need Some Modification

Myproblem

Board Regular

Excel Facts

pedie

Well-known Member

Myproblem

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock