Pivot Table Macro

[SCPbrito]

I am trying to create a macro that takes data from one worksheet called "Item Transaction" and creates a pivot table on another worksheet called "Pivot Table". I record the macro fine. But when going to test it I erase the pivot table it created and rerun it and im getting an error : Run-Time error '5': Invalid procedure call or argument. Below is the code that is highlighted why i go to debug. Any insight here?


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Item Transaction!R1C1:R1048576C30", Version:=8).CreatePivotTable _
TableDestination:="Pivot Table!R1C1", TableName:="PivotTable1", _
DefaultVersion:=8

 

[RoryA]

Why are you using entire columns of data? That is not efficient. I'd suggest something like:

With Sheets("Item Transaction")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(referencestyle:=xlR1C1), Version:=8).CreatePivotTable _
TableDestination:=Sheets("Pivot Table").Range("A1"),DefaultVersion:=8
End With

I'd also suggest assigning the result to a PivotTable object variable for further use in your code.

 

[SCPbrito]

sorry im a complete newbie when it comes to macros. Where would i input this in the code below and where would i manually adjust to the correct ranges


Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Item Transaction!R1C1:R1048576C30", Version:=8).CreatePivotTable _
TableDestination:="Pivot Table!R1C1", TableName:="PivotTable1", _
DefaultVersion:=8
Sheets("Pivot Table").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Time").AutoGroup
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Entry Type")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Quantity"), "Sum of Quantity", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Reason Code")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("VW Name")
.Orientation = xlRowField
.Position = 2
End With
End Sub

 

[RoryA]

Replace all of that with this:

   Application.CutCopyMode = False
   Dim pc As PivotCache
   With Sheets("Item Transaction")
      Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & .Name & "'!" & _
                        .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=8)
   End With
   
   Dim pt As PivotTable
   Set pt = pc.CreatePivotTable(TableDestination:=Sheets("Pivot Table").Range("A1"), DefaultVersion:=8)
   
   With pc
      .RefreshOnFileOpen = False
      .MissingItemsLimit = xlMissingItemsDefault
   End With

   With pt
      .ColumnGrand = True
      .HasAutoFormat = True
      .DisplayErrorString = False
      .DisplayNullString = True
      .EnableDrilldown = True
      .ErrorString = ""
      .MergeLabels = False
      .NullString = ""
      .PageFieldOrder = 2
      .PageFieldWrapCount = 0
      .PreserveFormatting = True
      .RowGrand = True
      .SaveData = True
      .PrintTitles = False
      .RepeatItemsOnEachPrintedPage = True
      .TotalsAnnotation = False
      .CompactRowIndent = 1
      .InGridDropZones = False
      .DisplayFieldCaptions = True
      .DisplayMemberPropertyTooltips = False
      .DisplayContextTooltips = True
      .ShowDrillIndicators = True
      .PrintDrillIndicators = False
      .AllowMultipleFilters = False
      .SortUsingCustomLists = True
      .FieldListSortAscending = False
      .ShowValuesRow = False
      .CalculatedMembersInFilters = False
      .RowAxisLayout xlCompactRow
      .RepeatAllLabels xlRepeatLabels
      With .PivotFields("Time")
         .Orientation = xlColumnField
         .Position = 1
         .AutoGroup
      End With
      With .PivotFields("Entry Type")
         .Orientation = xlRowField
         .Position = 1
      End With
      With .PivotFields("VW Name")
         .Orientation = xlRowField
         .Position = 2
      End With
   
      .AddDataField .PivotFields("Quantity"), "Sum of Quantity", xlSum
      
      With .PivotFields("Reason Code")
         .Orientation = xlPageField
         .Position = 1
      End With
   End With
End Sub

 

[SCPbrito]

Thank you soo much Rory. It's working perfectly