Pivot Table VBA Help

chaddres

Board Regular
Joined
Jun 14, 2014
Messages
91
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: 2

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,012
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
91
Thank you, Alex! I must have looked at the code 100 times and I missed that.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,012
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,143,692
Messages
5,720,313
Members
422,276
Latest member
streasure

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