VBA Worksheet.add in a pivot table

A

anthonyexcel

Active Member
Joined
Jun 10, 2011
Messages
258
Office Version
  1. 365
Platform
  1. Windows
Can someone please help me. The macro below works fine except the colored lines below. What I would like to be able to do is everytime I run this pivot table macro it places the pivot table on a new sheet. If the sheet exists make a new one. Thanks in advance!

Rich (BB code): 
Sub Macro1()
Application.ScreenUpdating = False
    Worksheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Report!R1C1:R34C27", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet5!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Group")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics Number Tested"), _
        "Sum of Total Mathematics Number Tested", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 1"), _
        "Sum of Total Mathematics % Lvl 1", xlSum
    With ActiveSheet.PivotTables("PivotTable1").DataPivotField
        .Orientation = xlColumnField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 2"), _
        "Sum of Total Mathematics % Lvl 2", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 3"), _
        "Sum of Total Mathematics % Lvl 3", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 4"), _
        "Sum of Total Mathematics % Lvl 4", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 5"), _
        "Sum of Total Mathematics % Lvl 5", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Goal Range"), _
        "Sum of Total Mathematics % Goal Range", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Proficient"), _
        "Sum of Total Mathematics % Proficient", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading Total Reading"), _
        "Sum of Total Reading Total Reading", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 1"), _
        "Sum of Total Reading % Lvl 1", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 2"), _
        "Sum of Total Reading % Lvl 2", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 3"), _
        "Sum of Total Reading % Lvl 3", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 4"), _
        "Sum of Total Reading % Lvl 4", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 5"), _
        "Sum of Total Reading % Lvl 5", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Goal Range"), _
        "Sum of Total Reading % Goal Range", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Proficient"), _
        "Sum of Total Reading % Proficient", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing Number Tested"), _
        "Sum of Total Writing Number Tested", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 1"), _
        "Sum of Total Writing % Lvl 1", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 2"), _
        "Sum of Total Writing % Lvl 2", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 3"), _
        "Sum of Total Writing % Lvl 3", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 4"), _
        "Sum of Total Writing % Lvl 4", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 5"), _
        "Sum of Total Writing % Lvl 5", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Goal Range"), _
        "Sum of Total Writing % Goal Range", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Proficient"), _
        "Sum of Total Writing % Proficient", xlSum
    With ActiveSheet.PivotTables("PivotTable1").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Still bombing out on the line below
Rich (BB code): 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
       "Report!R1C1:R34C27", Version:=xlPivotTableVersion10).CreatePivotTable _
       TableDestination:="sh!R3C1", TableName:="PivotTable1", DefaultVersion _
       :=xlPivotTableVersion10


I was able to add a worksheet but still cannot get this to work! Any suggestions?

Rich (BB code): 
Sub Macro1()
Application.ScreenUpdating = False
Dim sh As Worksheet
Set sh = Worksheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
       "Report!R1C1:R34C27", Version:=xlPivotTableVersion10).CreatePivotTable _
       TableDestination:="sh!R3C1", TableName:="PivotTable1", DefaultVersion _
       :=xlPivotTableVersion10
   sh.Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Group")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics Number Tested"), _
        "Sum of Total Mathematics Number Tested", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 1"), _
        "Sum of Total Mathematics % Lvl 1", xlSum
    With ActiveSheet.PivotTables("PivotTable1").DataPivotField
        .Orientation = xlColumnField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 2"), _
        "Sum of Total Mathematics % Lvl 2", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 3"), _
        "Sum of Total Mathematics % Lvl 3", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 4"), _
        "Sum of Total Mathematics % Lvl 4", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Lvl 5"), _
        "Sum of Total Mathematics % Lvl 5", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Goal Range"), _
        "Sum of Total Mathematics % Goal Range", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Mathematics % Proficient"), _
        "Sum of Total Mathematics % Proficient", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading Total Reading"), _
        "Sum of Total Reading Total Reading", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 1"), _
        "Sum of Total Reading % Lvl 1", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 2"), _
        "Sum of Total Reading % Lvl 2", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 3"), _
        "Sum of Total Reading % Lvl 3", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 4"), _
        "Sum of Total Reading % Lvl 4", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Lvl 5"), _
        "Sum of Total Reading % Lvl 5", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Goal Range"), _
        "Sum of Total Reading % Goal Range", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Reading % Proficient"), _
        "Sum of Total Reading % Proficient", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing Number Tested"), _
        "Sum of Total Writing Number Tested", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 1"), _
        "Sum of Total Writing % Lvl 1", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 2"), _
        "Sum of Total Writing % Lvl 2", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 3"), _
        "Sum of Total Writing % Lvl 3", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 4"), _
        "Sum of Total Writing % Lvl 4", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Lvl 5"), _
        "Sum of Total Writing % Lvl 5", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Goal Range"), _
        "Sum of Total Writing % Goal Range", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total Writing % Proficient"), _
        "Sum of Total Writing % Proficient", xlSum
    With ActiveSheet.PivotTables("PivotTable1").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
321
RoryA
RoryA
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
246
darmst24
D
O
  • Solved
Change the Pivot Table from another sheet
Replies
2
Views
168
Ongbey
O
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
252
Jette3173
J
S
  • Question Question
Macro for
Replies
2
Views
120
Sanderb
S

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top