Pivot Table VBA Help

chaddres

Board Regular
Joined
Jun 14, 2014
Messages
121
Office Version
  1. 365
Platform
  1. Windows
When I try to create my second pivot table (on a separate worksheet called 'Overhead'), I get an error: "Run-time error '1001'" A PivotTable report cannot overlap another PivotTable report. The reports are on separate worksheets. I can't figure out what I have wrong.

The code giving me the error is

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Time Export!R1C1:R701C8").CreatePivotTable TableDestination:= _
"Overhead!R2C2", TableName:="PivotTable2"


Any help is appreciated!


VBA Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Macro Workbook").Select
    Range("A1").Select
    'Sheets.Add After:=ActiveSheet
    'Sheets("Sheet5").Select
    'Sheets("Sheet5").Name = "Time Export"
    Sheets("Sheet 1").Select
    Range("A:A,C:C,D:D,G:G,H:H,P:P,V:V").Select
    Selection.Copy
    Sheets("Time Export").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Cells.Select
    Application.CutCopyMode = False
    Columns("A:H").Select
    Columns("A:H").EntireColumn.AutoFit
    Range("A1").Select
    Selection.CurrentRegion.Select
    With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("if(" & .Offset(, -1).Address & "=""Holiday"",8," & .Address & ")")
    End With
    With Range("F2:F" & Range("D" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("if(" & .Offset(, -2).Address & "=""Holiday"",8," & .Address & ")")
    End With
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Time Export'!R1C1:R1695C8").CreatePivotTable TableDestination:= _
        "'Time Export'!R2C10", TableName:="PivotTable1"
    Cells(2, 10).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("Resource")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Customer Hours/ Units"), _
        "Sum of Customer Hours/ Units", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Customer Hours/ Units2"), _
        "Sum of Customer Hours/ Units2", xlSum
    Sheets("Time Export").Select
    Range("A1").Select
    Selection.CurrentRegion.Select
   [B] ActiveWorkbook.Worksheets("Time Export").PivotTables("PivotTable1").PivotCache. _
        CreatePivotTable TableDestination:="Overhead!R2C2", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion12[/B]
    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 = True
        .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("Resource")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project")
        .Orientation = xlRowField
        .Position = 2
    End With
    Sheets("Overhead").Select
    Range("B2").Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Customer Hours/ Units"), _
        "Sum of Customer Hours/ Units", xlSum
    Sheets("Time Export").Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Time Export!R1C1:R701C8").CreatePivotTable TableDestination:= _
        "Overhead!R2C2", TableName:="PivotTable2"
    Sheets("Overhead").Select
    Cells(2, 2).Select
    With ActiveSheet.PivotTables("PivotTable2")
        .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("PivotTable2").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Resource")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Project")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Customer Hours/ Units"), _
        "Sum of Customer Hours/ Units", xlSum
End Sub
 

Attachments

  • 1625233464695.png
    1625233464695.png
    15 KB · Views: 8

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,624
Office Version
  1. 365
Platform
  1. Windows
You seem to be creating 2 pivot tables with a destination of ""Overhead!R2C2""

PivotTable 1
VBA Code:
ActiveWorkbook.Worksheets("Time Export").PivotTables("PivotTable1").PivotCache. _
        CreatePivotTable TableDestination:="Overhead!R2C2", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion12

PivotTable 2
VBA Code:
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Time Export!R1C1:R701C8").CreatePivotTable TableDestination:= _
        "Overhead!R2C2", TableName:="PivotTable2"
 
Solution

chaddres

Board Regular
Joined
Jun 14, 2014
Messages
121
Office Version
  1. 365
Platform
  1. Windows
Thank you, Alex! I must have looked at the code 100 times and I missed that.
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,624
Office Version
  1. 365
Platform
  1. Windows
Haha I know the feeling. I had the luxury of not needing all the code to work so I pared it down to just the pivot cache/table setup lines.
Thanks for letting me know it fixed it for you. Glad I could help.
 

Forum statistics

Threads
1,181,601
Messages
5,930,818
Members
436,763
Latest member
vbagivesmeaheadache

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
Top