Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

PivotTable Error: A pivot table cannot overlap another pivot table report

Hi!

My question is: I am trying to refresh a pivot table and I get an error message "A pivot table cannot overlap another pivot table report". This is a spreadsheet that was passed onto me from another co-worker. To be honest with you I know how to bulid a pivot table but I do not know how to manipulate one. I read online about macros but I am not sure how to set one up to find the problem and then how to go on and fix it.

Please advise.
Thank you!


Post your answer or comment

comments powered by Disqus
I am trying to add another row to a Pivot Table but unable to..how can I do that... as I right click to do an Insert.... it says it cannot do so.

I am trying to step through a macro, created using the recorder, that builds a pivot table based on another pivot table. When I use the step-through in the VB screen to test the macro, it goes straight to debug in the first set of instructions. The error message is "Run-time error 1004" Application-defined or object-defined error.

   
ActiveWorkbook.Worksheets("Summary").PivotTables("SummaryPivot").PivotCache. _
        CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion10
I've tried changing "PivotTable2" to "ControlTotals" knowing that each time I try and build the pivot table in testing it will increment by 1. No luck.

I've searched through the archives and googled tyring to find a resolution but so far have come up empty.
Any help would be apprecitated or point me to a potential resource.

Below is the complete code to build the macro, and yes I know it needs to be cleaned up. I just need to get past this first hurdle.
Sub Macro3()
'
    ActiveWorkbook.Worksheets("Summary").PivotTables("SummaryPivot").PivotCache. _
        CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Pay Code").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("PSID", _
        "Pay Code", "Data"), PageFields:="Batched"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("BaseHours")
        .Orientation = xlDataField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Amount").Orientation = _
        xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Batched").CurrentPage = _
        "Yes"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("PSID")
        .PivotItems("#N/A").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable2").Format xlReport4
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable2").Format xlPTClassic
    ActiveSheet.Name = "Control Totals"
    Range("A5").Select
    Sheets("Control Totals").Move After:=Sheets(5)
    Sheets("Control Totals").Select

End Sub
Thanks.

Hi Anyone,

I’ve created two pivot tables (pivot table 01 & pivot table 02) into one sheet by data taken from two separate worksheets of the same workbook.

The row in the pivot table 1 gets increased by one if I enter a new category name in the category column of the worksheet “Database” and as I keep entering new products to the sheet.

If the pivot table 02 is placed directly below the empty row after the pivot table 01, excel gives the warning message "Pivot Table cannot overlap Another Pivot Table".

To avoid this confusion, I had placed the second pivot table on row “54”and I had hidden all rows (rows 28:53) between the two pivot tables.

When I update the sheet “Database” after doing the above, the pivot table 01 gets updated but it does not unhide the hidden row instead it updates while keeping the row hidden.

My question is how I could create a worksheet event macro or any other formula that would update the pivot table 01 and unhide the row.

Any help would be kindly appreciated.

I have attached the workbook for your reference.

How can I create a calculated field that includes formulas with MIN, MAX or
AVERAGE?
Becasue the summary form calculated field of a pivot table cannot be changed
(is always SUM), Excel returns a different number when I try to insert a
field that includes such a formula.
I am trying to include a field like this to calculate minimum possible cost:
=MIN('Price')*SUM(Order Quantity)
Any suggestion on how to do it?

Hello friends!

Attached below is an example file, created to illustrate my issue. The first two columns (NAME and CAR) are a pivot table based on another table. The table right next to the pivot table is supposed to be information about the entries in the CAR column. I need these tables to work together in the sense that, any row removed from the pivot table should also be removed from the adjoining table. In short, if, for any reason, a row is removed from NAME and CAR, the same row under headings VEHICLE TYPE, MODEL NAME and COLOUR should be deleted as well. Also, if any new row is created under NAME and CAR (i.e. if any data is added to the first two columns) a new row should be added to the adjoining table.

Please see the attachment. Any help will be greatly appreciated. Thank you.

I created a pivot table for an aging report. It is broken down by not due,
0-30, 31-60, & 61+ days old by customer. When I double click in a cell say
31-60, I want to be able to see the items that make up that total. This is
bringing everything up on that customers account. What am I doing wrong?
Can I send my pivot table to someone to look at and help me?
Thank you

I created a pivot table for an aging report. It is broken down by not due,
0-30, 31-60, & 61+ days old by customer. When I double click in a cell say
31-60, I want to be able to see the items that make up that total. This is
bringing everything up on that customers account. What am I doing wrong?
Can I send my pivot table to someone to look at and help me?
Thank you

I'm trying to change the data source of an existing pivot table, but I get the message "a pivot table report cannot overlap another pivot table report". This is the only pivot table on the worksheet. This is my code. The error occurs on the line shown in red.

With Sheets("Analytic Table")
.PivotTables("PT-Analysis").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Input Table'!R1C21:R" & NumRows & "C22"
.Visible = True
.Select
.PivotTables("PT-Analysis").PivotCache.Refresh
.Visible = False
End With

Can anyone tell me how to reference the existing pivot table and just update its data source? TIA.

I used the macro recorder in Excel 2007 to record the code below. The code is supposed to use a .iqy to pull data from a SharePoint 2007 site. When I run the macro, I receive the following error: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table. I have no issue running the .iqy query from excel without using the macro. Any idea what's causing the 1004 error?

Sub Macro1()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;", Destination:=Range("A1"))
.QueryTable
.CommandType = 5
.CommandText = Array( "{9848425C-7548-45D7-97F6-0F39962103AD}{F0102C5F-57BF-4023-B2D0-FF93F04A0030}

I used the macro recorder in Excel 2007 to record the code below. The code is supposed to use a .iqy to pull data from a SharePoint 2007 site. When I run the macro, I receive the following error: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table. I have no issue running the .iqy query from excel without using the macro. Any idea what's causing the 1004 error?
Sub Macro1()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;", Destination:=Range("A1") _
).QueryTable
.CommandType = 5
.CommandText = Array( _

"{9848425C-7548-45D7-97F6-0F39962103AD}{F0102C5F-57BF-4023-B2D0-FF93F04A0030} , _

"LISTNAME>http://prod65-share2.mgn.netl.doe.gov:21928/_vti_bin , "R>/Lists/Heat Survey")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"I:CommonHELPDESKMetric ReportsHeatSurveyQuery.iqy"
.ListObject.DisplayName = "Table_HeatSurveyQuery"
.Refresh BackgroundQuery:=False
End With

End Sub


Hi,

I m using Excel 2010.
I used a macro recording to record the activity i.e., pull the data from a iqy file(web query file generated when you do Export to Excel in a SharePoint site to export SharePoint list data to Excel.). I want to run this macro on daily basis. However the problem is when you try to re- run the same macro I get error Run-time error '1004'
A table cannot overlap a range that contains a PivotTable Report, query results, protected cells or another table.

Any reasons why?

What would be the solution to my problem? Thanks.

RunTimeErr.JPG

Sub
Macro4()
'
' Macro4 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;", Destination:=Range("$A$1") _
        ).QueryTable
        .CommandType = 5
        .CommandText = Array( _
       
"<LIST><VIEWGUID>{50A58D53-347D-4E68-B9BC-CA834F7A068E}</VIEWGUID><LISTNAME>{A486016E-80B2-44C3-8B4A-8394574B9430}</"
_
        , _
       
"LISTNAME><LISTWEB>http://pd2012/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>/Lists/Projects
List</RO" _
        , "OTFOLDER></LIST>")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:Usersabcd2Downloadsowssvr.iqy"
        .ListObject.DisplayName = "Table_owssvr"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub


I keep receiving the following message in my pivot table. Can someone please
tell me what this means? "Pivot table reports cannot overlap another pivot
table"

Hey!

I run the following macro to process a pivot table. It works fine the first
time it is run. After I complete it and want to build another one it gives
a run time error.
I have to exit excel completely and reopen it in Book1 to get it to run all
the way thru. The problem codes are surrounded by ******
I want to be able to run it on the active work sheet no matter want file is
open. I have tried creating worksheets at the begining and the PivotCache
will not run at all.
I also want the "Select Database" from CreatePivotTables to go to specified
directory not the default if possible.

Sub Macro_Testing()

'
' Create_Pivot_Table Macro
' Macro recorded 2/19/2005 by Pete Straman
'
' Keyboard Shortcut: Ctrl+Shift+T

'Tried this stuff to if deleting a sheet and adding it would help run time
error on second time thru program

' Delete PivotSheet if it exists
'On Error Resume Next
'Application.DisplayAlerts = False
'Sheets("Sheet1").Delete
'On Error GoTo 0

' Add new worksheet
'Worksheets.Add
'ActiveSheet.Name = "Sheet1"

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "ODBC;DSN=MS Access Database;"
.CommandType = xlCmdSql
.CommandText = "SELECT trend_rpt.facilityid, trend_rpt.`Sum of
Revenue`," _
+ " trend_rpt.`Sum of Payments`, trend_rpt.`Sum of Adjustments`,
trend_rpt.transmoyr," _
+ " trend_rpt.dosmoyr" & Chr(13) & "" & Chr(10) & "FROM trend_rpt
trend_rpt" _

' *************************How do I set this to a specified directory?
****************
' *****************tried at .Connection without success

.CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

'************************************************************************************
End With

ActiveSheet.PivotTables("PivotTable1").ColumnGrand = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="dosmoyr", _
PageFields:="facilityid"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of
Revenue")
.Orientation = xlDataField
.Caption = "Revenue"
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With
Range("A5:A65").Select

Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=6,
_
Orientation:=xlTopToBottom

With ActiveSheet.PivotTables(1).PivotFields("facilityid")
.CurrentPage = .PivotItems(1).Value
End With

Columns("A:B").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PivotTableWizard TableDestination:="Sheet1!R1C3"

'******* This statement will cause a runtime error used in
'******* anything but Book1 - so I have to close and reopen excel to
get it to run
' Run time error 1004 unable to get the Pivot Tables property of the
worksheet class

ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="dosmoyr", _
ColumnFields:="transmoyr", PageFields:="facilityid"

'*********************************************************************************
'*********************************************************************************

With ActiveSheet.PivotTables(2).PivotFields("facilityid")
.CurrentPage = .PivotItems(1).Value
End With

ActiveSheet.PivotTables("PivotTable2").PivotFields("Revenue")
..Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of
Payments")
.Orientation = xlDataField
.Caption = "Payments"
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With
ActiveSheet.PivotTables("PivotTable2").DataPivotField.PivotItems
("Payments"). _
Position = 1
Columns("C:C").Select
Selection.EntireColumn.Hidden = True

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pivot Table"
Sheets("Pivot Table").Select
Sheets("Pivot Table").Copy Before:=Sheets(1)
Sheets("Pivot Table (2)").Select
Sheets("Pivot Table (2)").Name = "Collections"
Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Collections").Select
Application.CutCopyMode = False
Sheets("Collections").Move Before:=Sheets(3)
Sheets("Pivot Table").Select

End Sub

--
Message posted via http://www.officekb.com

Hi,
Please let me know if there is a better place to post this kind of a technical question.
I have a .xlsm Excel 2007 workbook (I did not try it with a .xlsx). When i open it by double clicking it from windows explorer, there is no issue. When the file is streamed from a website to the browser (with this header - response.addHeader("content-disposition", "attachment; filename=" + some-file-name)) and opened (instead of saved when the browser prompts you), i get a "Security Warning - Data connections have been disabled" warning and the [Options...] button. This issue started after adding a Pivot Table to the file (I also tried it with a very simple .xlsm to confirm the behavior and the cause).

Clicking the Options button brings up a "Security Alert - Data Connection" popup with 2 radio button options: (1) help protect me from unknown content (recommended), and (2) enable content.
selecting (1) and click ok
select pivot table and refresh, get this popup error: .... security concern. The operation connects to an external data source..
when i click OK, i get another popup error: "Cannot open PivotTable source file 'C:Documents and SettingsUSERID.......[......some-file-name [1].xlsm]Sheet1'", with an OK button, if i click ok, the pivot table does not update
(Note: if both the server where the document was streamed from, and the client (the machine where the excel spreadsheet is opened) are the same machine, I don't get the last popup "Cannot open pivot table ....".

I would appreciate any help you can offer. I have a combo box that has 7 items. a,b,c,d,e,f,g. I have code that will change the page field of a pivot table. when i change the combo box. Selecting individual entries works fine. I cannot seem to get the table to recognize "ALL". using pf.currentpage=_"(ALL)" doesn't seem to work. i have included the code i have.(minus any reference to all) Thanks in advance for any help.


	VB:
	
 
Sub FilterREGIONS() 
     'This will add current region to pivots
     
    Dim pt As PivotTable 
    Dim pf As PivotField 
    Dim pi As PivotItem 
     
    Application.ScreenUpdating = False 
    On Error Resume Next 
     'region info
    ThisWorkbook.Sheets("pivot").PivotTable("weeklydata").PivotCache.Refresh 
    region = Sheets("infosheet").Range("$f$6").Value 
    Set pt = ThisWorkbook.Sheets("pivot").PivotTables("weeklydata") 
    Set pf = pt.PivotFields("region") 
    pt.ManualUpdate = True 
    pf.EnableMultiplePageItems = False 
    For Each pi In pf.PivotItems 
        pi.Visible = True 
    Next pi 
    For Each pi In pf.PivotItems 
        If pi.Value  region Then 
            pi.Visible = False 
        End If 
         
    Next pi 
    pt.ManualUpdate = False 
    Set pf = Nothing 
    Set pt = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I figured out how to update a pivot table from a form combo box with the following code:
Code:

	VB:
	
 SelectEmployeeCombo_Change() 
    Sheets("CommandCenter").PivotTables("ReceivingPT").PivotFields("Employee").ClearAllFilters 
    Sheets("CommandCenter").PivotTables("ReceivingPT").PivotFields("Employee").CurrentPage = _ 
    Sheets("Info").Range("C1").Value 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The problem I have now is if the name is not in the list from the combo box the program crashes gives me a error code. Run-Time error '1004" Application-defined or object-defined error. When I reset and pick a name from the list it works. It would be great if the name is not in the list from the combo box you get a message code Name not in list.

If someone can direct me with a error handling line to correct this. Do I use and If statement? I been searching Google but have no direction on how to fix.

Thanks

Hello,

I am trying to create a pivot table macro so that I can produce a report of a senerio on one worksheet, from calculations on another worksheet. In other words, the basic function of the workbook is to create reports of various scenarios without using scenario manager.

I want to do this because the format of scenario manager is terrible and it makes it difficult organize my results.

So, I am trying to create a macro for the first time using pivot tables. The macro should run, then create a new column on my customized summary sheet where a pivot table is created so I can just get the values of the scenario, rather than the reference to the calculations on the other sheet. Once I have just the reported values I want the macro to put those values into specfied fields on my customized summary sheet.

I've tried creating this macro but keep running into this error:

"Run-time error '5': Invalid procedure call or argument"

Here is my code:

Sub German_Scenario()
'
' German_Scenario Macro
' Creates report for German Summary Sheet.
'
'
ActiveWindow.SmallScroll Down:=-21
ActiveCell.Offset(-7, 0).Range("A1").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-9
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Inputs & Outputs!R9C4:R22C4", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Germany Summary!R35C4", TableName:= _
"PivotTable23", DefaultVersion:=xlPivotTableVersion12
Sheets("Germany Summary").Select
Cells(35, 4).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable23").PivotFields("value")
.Orientation = xlRowField
.Position = 1
End With
ActiveWindow.SmallScroll Down:=-15
ActiveCell.Offset(-31, 0).Range("A1").Select
ActiveWindow.SmallScroll Down:=24
ActiveCell.Offset(32, 0).Range("A1:A13").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-24
ActiveCell.Offset(-32, 0).Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=27
ActiveCell.Offset(47, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Inputs & Outputs!R10C7:R15C7", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Germany Summary!R51C4", TableName:= _
"PivotTable24", DefaultVersion:=xlPivotTableVersion12
Sheets("Germany Summary").Select
Cells(51, 4).Select
ActiveWindow.SmallScroll Down:=9
ActiveCell.Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Inputs & Outputs!R9C7:R15C7", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Germany Summary!R51C4", TableName:= _
"PivotTable25", DefaultVersion:=xlPivotTableVersion12
Sheets("Germany Summary").Select
Cells(51, 4).Select
ActiveSheet.PivotTables("PivotTable25").AddDataField ActiveSheet.PivotTables( _
"PivotTable25").PivotFields("value"), "Sum of value", xlSum
With ActiveSheet.PivotTables("PivotTable25").PivotFields("Sum of value")
.Orientation = xlRowField
.Position = 1
End With
ActiveCell.Offset(1, 0).Range("A1:A6").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-24
ActiveCell.Offset(-34, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(8, 3).Range("A1").Select
ActiveWindow.SmallScroll Down:=-12
End Sub

Thanks!

While trying to add a calculated item to a pivot table I get the
following error

"The item cannot be added or modified. There may not be enough memory,
the maximum number of items allowed in a field may have been reached,
or if a Visual Basic macro is performing the operation, the macro may
be incorrect

Simply the Pivot Table or check the macro for errors, and then try the
operation again."

I am doing this manual so the macro issue is not the problem. It must
be the memory issue, but how do I know how much memory is being used.
The workbook has about 7 pivot tables in it and it's a smaller version
of the same file and therefore is using less memory (which is the
point of recreating the old file). How do I find out if it's the
memory issue. Also my pivots all pull off the same data.

John

A user has sent me an Excel document (349kb). It is a pivot table with drop down options at the top and and a second sheet, lots of data. When she tries to refresh the data she gets: “Excel cannot complete this task with available resources, choose less data or close other applications”.

If I open this in Excel 2003 (fully-updated, sp2 e.t.c), right-click anywhere on the sheet and select refresh it says “there are too many records to complete this operation. I think the refresh is supposed to show new totals or something like that.

I tried 2 machines at work, both running Excel 2002 (she also runs 2002, SP3) and when I attempt to do the same thing I get:

“Excel cannot complete this task with available resources, choose less data or close other applications”. It then asks if I want to continue with no undo option, but it just produces another error and gives up. At first I though it was a lack of memory, but the other machines I tried it on have plenty and all have the same issue.

Thing is, I emailed it to another person and she said she could refresh it! I can’t figure it out! I didn’t have time to check her machine, but I’m sure I’ll find no obvious difference with anything. It must be a setting in Excel, but I doubt I’ll find it anytime soon.

Any help with this one would be much appreciated. Thanks.

I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation. Please advise.
Sub Step_5ab()

 Dim DstWkb As Workbook
  Dim SrcWkb As Workbook
  Dim Rng As Range, Dn As Range, n As Integer
  Dim RngEnd As Range
  Dim Pt As PivotTable
  Dim strField As String

  Set Rng = Selection
  
  With Application
        .ScreenUpdating = False
        .EnableEvents = False
  End With
    
    Set DstWkb = Workbooks("NewDirection.xls")
    Set SrcWkb = Workbooks("ESS.xls")
    
    
    DstWkb.Activate
        
  'Copy and paste the Procedure - Okay
    Sheets("Sheet1").Range("A254").EntireRow.Copy
    Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
  SrcWkb.Worksheets("FY 10 by PAC").Activate

    'Copy and paste all of the ESS data -Okay
        Range("A1:N144").Copy _
                Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
          
   'Copy and paste a blank row - Okay
     DstWkb.Worksheets("Sheet1").Range("A272").Copy
     DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'Create pivot for APR Data-Here's the problem
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "APRData!C1:C23").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("PAC", _
        "Job Title", "Unit")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("FTE").Orientation = _
        xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
    Range("C4").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit")
        .Orientation = xlColumnField
        .Position = 1
    End With

    
    'Label, copy, and paste
    
     'Copy only the filtered data
           Range("A1").Activate
        Rng.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
                
              
    'Copy and paste the Solution
        Sheets("Sheet1").Range("A265").EntireRow.Copy
        Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    'Copy two blank rows
     Sheets("Sheet1").Range("A272:A273").Copy
     Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
          
        'Alert the user on progress
        MsgBox "Step 5 Compare FTE Position totals to ESS is complete; the results have been recorded"
          Run "Step_6a"

End Sub


Hello,

I have a pivot table that I filter through VBA. The problem is that when I delete the values in the range that the pivot table uses as data source, it still shows the values in the columns dropdown (even if I refresh) and when there is data in the range, the dropdown shows data that doen't exist in such range (the column Year plan only has 2012 values but it shows the 2013 option too, and when you select it the pivot table can't show any data).

The filter area, nevertheless, works correctly and data dissapear when I delete tha values in the data range.

I have checked another excel file and I see that a pivot table in there works the way I wish: no data - empty dropdown in columns and rows.

The code I use to control the pivot table is:

Sub createExpensesPT(Optional CalcPivot As
Boolean, Optional sendprog As Variant, Optional sendyear As Variant)



elemfound = False

Set pt = Worksheets("Reports").PivotTables("PivotExpenses")
    
pt.RefreshTable

pt.ManualUpdate = True


'________________________ FILTERING OF THE PIVOT TABLE
___________________________________________________________________________

filterName = "Programs"
Debug.Print " "
Debug.Print "========= Expenses by program Chart ============"
Debug.Print "filtername is " & filterName
'Debug.Print "elementName is " & elementName


Set pf = pt.PivotFields(filterName)
With pf
    .AutoSort xlManual, .SourceName
    Debug.Print "The source name of the field is: " & .SourceName
    
    '####### IF NO PROGRAMS WERE SELECTED WE MAKE ALL OF THEM VISIBLE ########
    'We make all the elements visible and we warn the user.
    If CalcPivot = False Then
        GoTo makeallvisible
    End If
    '####### MAKING THE SELECTED PROGRAMS VISIBLE ########
    If sendprog(0) <> "" Then
        For i = 0 To UBound(sendprog, 1)
            elementName = sendprog(i)
            For x = 1 To .PivotItems.Count
                itemstr = .PivotItems(x).Name
                Debug.Print "Checking for " & itemstr & " in the " & .SourceName & "
field."
                If itemstr = elementName Then
                    On Error Resume Next
                    .PivotItems(x).Visible = True
                    On Error GoTo 0
                    Debug.Print "Values for " & .PivotItems(x).Name & " are VISIBLE."
                    elemfound = True
                    Debug.Print "elemfound is " & elemfound
                    If titlestr = "" Then
                        titlestr = elementName
                    Else
                        titlestr = titlestr & ", " & elementName
                    End If
                    Exit For
                ElseIf x = .PivotItems.Count Then
                    prognotfound = True
                    Debug.Print "the program " & elementName & "  wasn't found in the " &
.SourceName & " field."
                    If notfoundstr = "" Then
                        notfoundstr = Chr(13) & Chr(149) & " " & elementName
                    Else
                        notfoundstr = notfoundstr & Chr(13) & Chr(149) & " " & elementName
                    End If
                End If
            Next
        Next
        Sheets(Reports_sheet).Cells(12, 2).Value = " Expenses by Program: " & titlestr
    End If
    
    '####### HIDING THE OTHER PROGRAMS ########
    'We go through the pivot field items and go through the programs array searching for the elements.
    'If we don't find the the pivot item in the array we hide it.
    If elemfound = True Then
        Debug.Print "elemfound was true, so we hide the other items in the " & .SourceName & "
field."
        For x = 1 To .PivotItems.Count
            itemstr = .PivotItems(x).Name
            For i = 0 To UBound(sendprog, 1)
                If itemstr = sendprog(i) Then
                    Debug.Print itemstr & " = " & sendprog(i)
                    Exit For
                ElseIf i = UBound(sendprog, 1) Then
                    Debug.Print itemstr & " is HIDDEN."
                    .PivotItems(x).Visible = False
                End If
            Next
        Next
    Else
'----> We make all the programs visible
makeallvisible:
        For Each ptItem In pf.PivotItems
            If ptItem.Visible <> True Then
                On Error Resume Next
                ptItem.Visible = True
                On Error GoTo 0
                Debug.Print "Values for " & ptItem.Name & " are VISIBLE."
            End If
        Next
    End If
    .AutoSort xlAscending, .SourceName
End With


'####### FILTERING BY YEAR ACCORDING TO THE YEAR ARRAY ########

If sendyear(0) <> "All data" Then
    Set pf = pt.PivotFields("Year Plan")
    With pf
        .AutoSort xlManual, .SourceName
        Debug.Print "The source name of the field is: " & .SourceName
        For x = 1 To .PivotItems.Count
            itemstr = .PivotItems(x).Name
            Debug.Print "itemstr is: " & itemstr
            For i = 0 To UBound(sendyear, 1)
                If itemstr = sendyear(i) Then
                    On Error Resume Next
                    .PivotItems(x).Visible = True
                    On Error GoTo 0
                    Debug.Print itemstr & " is the same as " & sendyear(i) & " so we make it
VISIBLE."
                    Exit For
                ElseIf i = UBound(sendyear, 1) Then
                    Debug.Print .PivotItems(x).Visible
                    On Error Resume Next
                    If .PivotItems(x).Visible = True Then .PivotItems(x).Visible = False
                    On Error GoTo 0
                    Debug.Print itemstr & " wasn't found in sendyear() so we make it HIDDEN."
                End If
            Next
        Next
    End With
Else
    Set pf = pt.PivotFields("Year Plan")
    With pf
        .AutoSort xlManual, .SourceName
        Debug.Print "The source name of the field is: " & .SourceName
        For Each ptItem In pf.PivotItems
            If ptItem.Visible = False Then ptItem.Visible = True
            Debug.Print "Values for " & elementName & " are VISIBLE."
        Next ptItem
    End With
End If
   


pt.PivotFields("Quarter Plan").AutoSort xlAscending, "Quarter Plan"

pt.ManualUpdate = False




End Sub
I would much appreciate your help.

I'm trying to create a Pivot Table with 2 data ranges to check redundancies
in my data. One data range is 51600 rows and the other is @ 62250 rows long.
When I try to create the Pivot Table, the system simply just tells me that it
cannot create the table due to a field in the source data has more uniques
items that can be used in a Pivot Table. NEED HELP! I'm not sure what this
error means since it doesn't clearly point out to me where the problem
manifests. IF someone could let me know if there's another formula I can use,
I'd greatly appreciate it!

Hi-

I have a pivot table...and the last cell in the table (bottom right
corner) is the Grand Total. This is a dynamic pivot table that
refreshes on open....so in order to preserve that cell I gave it a
name. The name for the cell is "Total_ABS". I have this code for
"Worksheet_Activate":

Range("Total_ABS").Select
ActiveCell.FormulaR1C1 = "='ADMIN&BUSSERV'!R[-1]C[1]"

The code errors on the second line of code. What I am trying to do is
go to the cell named "Total_ABS" and then make the cell 1 row above and
one column to the right the active cell. When I attempt to do this
using this code I get this error:

"Cannot enter a formula for an item or field name in a PivotTable
Report."

Any ideas on how to get by this?

TIA,
Chris

I am trying to create a macro to run with a button click that will refresh a pivot table that is located in a protected sheet. I had created the macro and it worked before I protected the sheet. Here is the code I have for it right now:

Sub OptionButton4_Click()

End Sub
Sub boxpivottablerefresh()
'
' boxpivottablerefresh Macro
' Refreshes the Boxes Pivot Table
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Range("K3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    MsgBox "Box quantities have successfully been calculated.", vbExclamation + vbInformation, "Calculation
Complete"
End Sub
Can someone please tell me the correct code to enable this function to work even when the sheet is protected? When I press the button right now, a pop-up appears that says:

"Run-time error '1004':

Cannot edit PivotTable on protected sheet."

With options to End, Debug, and Help.

Thanks in advance for the help!
Gavin


No luck finding an answer? You could always try Google.