Pivot Table VBA Help

C

chaddres

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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"
 
Upvote 0
Solution
Thank you, Alex! I must have looked at the code 100 times and I missed that.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA Pivot Table
Replies
2
Views
622
chaddres
C
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
639
Steve48
S
S
  • Question
Why won't a macro for a pivot table run on an identical workbook?
Replies
0
Views
614
superbuttz
S
T
  • Question
changing range for pivot macro
Replies
0
Views
479
Tonyk1051
T
T
  • Question
VBA Dynamic PivotTable Name
Replies
4
Views
493
th259
T

Forum statistics

Threads
1,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

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