gdunn59
asked on
Why is my Pivot Table VBA Code creating more than 1 Sheet/Pivot Table
I have written VBA code (macro) in Excel that creates a Pivot Table, it seems to be working the only problem is that it creates 2 sheets. The first one is blank, and the second one contains the Pivot Table that I want.
Also, every time I run the code/macro it increases the Sheet Number. I want the final Sheet that contains the Pivot Table to be named "Summary".
I have included the Code/Macro.
What is wrong?
Thanks,
gdunn59
Also, every time I run the code/macro it increases the Sheet Number. I want the final Sheet that contains the Pivot Table to be named "Summary".
I have included the Code/Macro.
What is wrong?
Thanks,
gdunn59
Sub macQtrlyAssocReport()
' macQtrlyAssocReport Macro
' Create Pivot Table
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Detail!R1C1:R270C7", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="", TableName:="Summary", DefaultVersion _
:=xlPivotTableVersion12
ActiveSheet.Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Summary")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
With ActiveSheet.PivotTables("Summary").PivotFields( _
"Quality_Review_Criteria")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Employee")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("InquiryNum"), "Associate Errors", xlCount
With ActiveSheet.PivotTables("Summary").PivotFields("Manager_Name")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Assoc Ops Area")
.Orientation = xlPageField
.Position = 2
End With
Range("A2").Select
With ActiveSheet.PivotTables("Summary").PivotFields("Manager_Name")
.Orientation = xlPageField
.Position = 2
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Assoc")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("Summary").PivotFields("Assoc"). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("Summary").PivotFields("Assoc").CurrentPage = _
"Y"
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Remove sheets.add on line 94.
ASKER
nutsch:
When I remove sheets.add on line 94, I then get the following error:
Run-time error '1004'
Application-defined or object-defined error
Thanks,
gdunn59
When I remove sheets.add on line 94, I then get the following error:
Run-time error '1004'
Application-defined or object-defined error
Thanks,
gdunn59
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I would like the points changed back to 300 since I offered to up the points to 500 if Expert nutsch could assist me in figuring out the other 2 issues that I was having with the extra blank tabs/sheets being inserted, and the issue with the borders.
So the points that should be awarded to Expert nutsch is the original 300 points.
Thanks,
gdunn59
So the points that should be awarded to Expert nutsch is the original 300 points.
Thanks,
gdunn59
ASKER
Here is the latest code:
Open in new window
Can you please assist me with the issue of it inserting the extra blank tab?
Thanks,
gdunn59