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

[excel5028000]

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!

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

 

[mole999]

give each pivot more room than you think, then they can expand as you apply changes

 

[excel5028000]

How?

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

 

[excel5028000]

mole999 and all. Please disregard. I got it solved. Thank you forum!

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