Clearing the cache || Preventing PivotTable overlap || Run-time error '1004':

excel5028000

New Member
Joined
Mar 5, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello All,

I am trying to insert/create a PivotTable on a Worksheet with 11 sheets via a for loop.
End goal is to insert one PivotTable in each sheet.
The source data on each sheet is located from column A thru F ... column-wise.
However row-wise (vertically), the numbers of rows vary. But I don't think this should affect anything.
Trying to inset the PivotTable on column H of each sheet.
The VBA macro works for the first sheet, but stops with error message shown below when attempting to continue loop....


Run-time error '1004':
A PivotTable report cannot overlap another PivotTable report.


I suspect my error has to do with clearing a cache. However I am lost. Any input? Please help. --Thank you.
Here's a hodgepodge of code gathered from internet. Thank you internet! and sample copy of the data in each sheet. Thanks again!

Image Excel.JPG



VBA Code:
Option Explicit

Sub ApplyPivotOnSameSheet_TestCode()


Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count
         
      ' Begin the loop.
         For I = 1 To WS_Count  '*******************************************************************

    Dim wsTarget As Worksheet
    Dim rngData As Range
    Dim rngPivotTarget As Range
    Dim objCache As PivotCache
    Dim objTable As PivotTable
    Dim objField As PivotField

    'create pivot table in code (no wizard) on same sheet
    Set wsTarget = ThisWorkbook.Sheets(ActiveSheet.Name)
    'set range to build table from
    Set rngData = wsTarget.Range("A1").CurrentRegion
    'set range for left-hand corner of pivot clear of source data by 1 column
    Set rngPivotTarget = wsTarget.Cells(1, rngData.Columns.Count + 2)
    'create cache from data
    Set objCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngData)
    'create table from cache
    Set objTable = objCache.CreatePivotTable(rngPivotTarget)

    'your original code
    Set objField = objTable.PivotFields("Description of Activity")
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("Month")
    objField.Orientation = xlColumnField

    Set objField = objTable.PivotFields("Period Amount")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = " $###,###,###"
    
    objTable.ColumnGrand = True
    objTable.RowGrand = False
    
    
   
    
''' Clearing the cache issues ***here  ***

     
    Set objCache = Nothing
    Set objTable = Nothing
    Set objField = Nothing
    Set wsTarget = Nothing
    Set rngData = Nothing
    Set rngPivotTarget = Nothing
    

 Next I
 End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
give each pivot more room than you think, then they can expand as you apply changes
 

excel5028000

New Member
Joined
Mar 5, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How?

Please show me in code. Come on mole999 help out. Thank you.
 

excel5028000

New Member
Joined
Mar 5, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
mole999 and all. Please disregard. I got it solved. Thank you forum!




VBA Code:
Option Explicit

Sub ApplyPivotOnSameSheet()

Dim WS_Count As Integer

Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active workbook.


Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning

    WS_Count = ActiveWorkbook.Worksheets.Count
         
     
    Dim wsTarget As Worksheet
    Dim rngData As Range
    Dim rngPivotTarget As Range
    Dim objCache As PivotCache
    Dim objTable As PivotTable
    Dim objField As PivotField





' Begin the loop.
    For I = 1 To WS_Count  '*******************************************************************

    ThisWorkbook.Worksheets(I).Activate


    'create pivot table in code (no wizard) on same sheet
    Set wsTarget = ThisWorkbook.Sheets(ActiveSheet.Name)
    
    'set range to build table from
    Set rngData = wsTarget.Range("A1").CurrentRegion
    
    'set range for left-hand corner of pivot clear of source data by 1 column
    Set rngPivotTarget = wsTarget.Cells(1, rngData.Columns.Count + 4)
        
        
    'create cache from data
    Set objCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngData)
    'create table from cache
    Set objTable = objCache.CreatePivotTable(rngPivotTarget)

    'your original code
    Set objField = objTable.PivotFields("Description of Activity")
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("Month")
    objField.Orientation = xlColumnField
    'Range("I2:J2").VerticalAlignment = xlCenter   'QUESTION THE VERTICAL ALIGNMENT
    Range("K2:L2").HorizontalAlignment = xlCenter   'QUESTION THE VERTICAL ALIGNMENT


    Set objField = objTable.PivotFields("Period Amount")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = " $###,###,###"
    
    objTable.ColumnGrand = True
    objTable.RowGrand = False
    
    
    
    ''' Clearing the cache issues ***here  ***

     
    Set objCache = Nothing
    Set objTable = Nothing
    Set objField = Nothing
    Set wsTarget = Nothing
    Set rngData = Nothing
    Set rngPivotTarget = Nothing
    


 Next I
 
 
 starting_ws.Activate 'activate the worksheet that was originally active
 
 End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,419
Members
415,839
Latest member
Pollydooner

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