Pivot Table VBA Help

[chaddres]

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!

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

 

[Alex Blakenburg]

You seem to be creating 2 pivot tables with a destination of ""Overhead!R2C2""

PivotTable 1

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

PivotTable 2

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

 

[chaddres]

Thank you, Alex! I must have looked at the code 100 times and I missed that.

 

[Alex Blakenburg]

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.