Push Button Pivot Table

[flipcube]

Hi All,

Need some ideas on how to go about accomplishing this. I have a userform that I would like to add a commandbutton to that creates a Pivot Table. The code below creates a pivot table on a specfic sheet. How would I modify this code to create a PivotTable on the next available unnamed sheet?

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Raw_Data", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet2").Select
Cells(3, 1).Select
Sheets("Sheet2").Name = "CLINS 1_17_18_19 - DLR" 'renames sheet
Call Pivot_Data(FiscalMonth)
CLIN_1_17_18_19
Call Format_Pivot(FiscalMonth)

 

[Trevor G]

You have a couple of issues here, first you have to look at the Pivot Table Name, then perhaps you could add a new sheet and set that as the activesheet command.

Perhaps you can adjust this

TableDestination:="Sheet2!R3C1",

to
Sheets.Add
Then your code

TableDestination:="",

 

[flipcube]

Trevor G,

here's what I came up with...

Private Sub CommandButton1_Click()
Dim sh As Worksheet, flg As Boolean
Dim sheetname As String

For Each sh In Worksheets
sheetname = TextBox1.Value
If sh.Name Like sheetname Then flg = True: Exit For
Next
If flg = True Then
MsgBox "Pivot Table already exists, please choose another name"
Else
Sheets.Add.Name = "Sheet1"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Raw_Data", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet1").Activate
Cells(3, 1).Select
Sheets("Sheet1").Name = sheetname 'Renames Sheet1 with TextBox1 value
Pivot_Data (FiscalMonth)
Format_Pivot (FiscalMonth)
Compress_Pivot
End If
End If

MsgBox "You're Pivot Table has been created"
End Sub

Codes works great when I create the first pivot table - but if I create a second, I get a Run Time error 1004 - Application defined or object defined error when it enters the Pivot_Data module. Below is the Pivot_Data module code up to the point where I get the error (last line is where I get the error). I read the help menu, but I cannot make sense of it...i'm still new to VBA

Public Sub Pivot_Data(FiscalMonth As String)
'This Subroutine populates the Pivot Tables

For Each sh In Worksheets
For Each PT In sh.PivotTables
If sh.Name <> "RAW" Or sh.Name <> "Fiscal_Calendar" Then
sh.Activate
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CLIN")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ACRN")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DELIVERY ORDER")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DESCRIPTION")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SUBSLIN")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"ESTIMATE TO COMPLETE")
.Orientation = xlRowField
.Position = 5
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("FUNDED"), "FUNDED TO DATE", xlSum

any help/suggestion greatly appreciated...

 

[flipcube]

Figured out the problem...

I have multiple pivot tables on multiple worksheets. Contained within each of my modules was the following code...

For Each sh In Worksheets
For Each PT In sh.PivotTables
If sh.Name <> "RAW" Or sh.Name <> "Fiscal_Calendar" Then
sh.Activate

...Code...

End if
Next pt
next sh

My program was checking every worksheet for the code contained after sh.Activate, which already existed within the Pivot Table, once it found it...it errored. I removed the above For Each Next loop statements from all my modules and it works fine.