Pivotcache.source Data Error 1004
Contents |
soon) Ruby (coming soon) Getting Started Code Samples Resources Patterns and Practices App Registration Tool Events Podcasts Training API Sandbox Videos Documentation Office Add-ins Office Add-in Availability Office Add-ins Changelog Microsoft Graph API Office 365 Connectors Office 365 REST APIs run time error 1004 pivot table field name is not valid SharePoint Add-ins Office UI Fabric Submit to the Office Store All Documentation https://www.yammer.com/ http://feeds.feedburner.com/office/fmNx Ask
Excel Vba Pivot Table Refresh Error 1004
a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: run time error vba runtime error 1004 pivot table field name is not valid 1004 while updating pivot-table Microsoft Office for Developers > Excel for Developers Question 0 Sign in to vote i'm using the same pivot table for the last 2 years, today i got this error, when i try run time error 1004 reference is not valid pivot table to debug i got to this line "ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh" Moved by Franklin ChenMicrosoft employee Friday, April 11, 2014 2:45 AM Excel Thursday, April 10, 2014 7:07 PM Reply | Quote Answers 0 Sign in to vote Hello Joe, Could you please be more specific? Excel version? First of all, I'd recommend breaking the chain of property and method calls into separate lines. Thus, you will be able to find the problematic property or method call.
Runtime Error 1004 Pivot Table Refresh
Is it the Refresh method? Try to specify the SourceData for the PivotCache object: With shPivot.PivotTables(1).PivotCache .SourceData = rng.Address(True, True, xlR1C1, True) .Refresh End With Also you can try to use the index instead of named pivot table: Worksheets(1).PivotTables(1).PivotCache.Refresh Does it work? Anyway, may find the following similar forum threads helpful: VBA Updating Source Data for Pivot Table to end of row Run-Time Error 1004 On Pivot Table Macro run-time error 1004 Marked as answer by Marvin_Guo Friday, April 18, 2014 1:43 AM Friday, April 11, 2014 9:10 AM Reply | Quote All replies 0 Sign in to vote Hi, Can you refresh the table manually? You should check the pivot table is existing in the active worksheet.If not, there will be an error as follows: Regards, MarvinWe are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey. Friday, April 11, 2014 8:15 AM Reply | Quote 0 Sign in to vote Hello Joe, Could you please be more specific? Excel version? First of all, I'd recommend breaking the chain of property and method calls into separate lines. Thus, you will be able to find the problematic property
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and refreshtable method of pivottable class failed excel 2010 policies of this site About Us Learn more about Stack Overflow the company run-time error '1004' unable to get the pivotfields property of the pivottable class Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Vba Code To Refresh Pivot Table
Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a https://social.msdn.microsoft.com/Forums/office/en-US/77d1f0d0-e07d-498a-8650-039e8eaaf284/run-time-error-1004-while-updating-pivottable?forum=exceldev minute: Sign up PivotTable error 1004 up vote 1 down vote favorite Can anyone see why this code would cause a 1004 error on the last line? Everything works great up until that last line. I had it working, then it started getting this error and I can't figure out why. Sheet2 is a blank sheet. Sheet1 is currently just test http://stackoverflow.com/questions/16312123/pivottable-error-1004 data, 10 rows, 3 columns. It starts at B3. Anyone have any ideas? Sub CreatePivot() ' Define RngTarget and RngSource as Range type variables Dim RngTarget As Range Dim RngSource As Range Dim intLastCol As Integer Dim intCntrCol As Integer ' RngTarget is where the PivotTable will be created (ie: Sheet2, Cell B3) Set RngTarget = ThisWorkbook.Worksheets("Sheet2").Range("B3") ' RngSource defines the Range that will be used to create the PivotTable ' ActiveWorkbook = The currently opened Workbook ' ActiveSheet = The currectly opened sheet ' UsedRange = The Range of cells with active data in them Set RngSource = ActiveWorkbook.ActiveSheet.UsedRange ' Select the Range RngSource.Select ' Copy the Range into the clipboard RngSource.Copy ' Create a new PivotTable using the RngSource defined above, ' in Excel format, ' placed at the RngTarget location, ' And name it PivotB3 just for reference if needed ActiveWorkbook.PivotCaches.Create(xlDatabase, RngSource).CreatePivotTable RngTarget, "PivotB3" ' Get the last used column from the data table intLastCol = RngSource.Columns(RngSource.Columns.Count).Column ' Select the Pivot table so we can apply the conditional formats ActiveSheet.PivotTables("PivotB3").PivotSelect "", xlDataAndLabel, True excel vba excel-vba pivot-table share|improve this qu
data of sheet2 by simply writing the VBA code. Data of sheet2 are frequently changing. The recorded macro is below. sub macro() error 1004 Sheets("Overview").Select ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "C:\Desktop\[asd.xlsm]RAW DATA!R1C1:R30C6", Version:= _ xlPivotTableVersion14) Range("C20").Select ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh End Sub Advance thanks xld03-04-2013, 02:33 PMSub macro() With Sheets("Sheet1") .PivotTables("PivotTable4").ChangePivotCache _ ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Worksheets("Sheet2").UsedRange, _ time error 1004 Version:=xlPivotTableVersion14) End With ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh End Sub abraham3003-05-2013, 10:39 AMHello Xld, Its working perfectly. One simple query. Suppose I run the macro in active sheet "sheet2". It refreshes the pivot but I am getting error like Run-time error '1004' Unable to get the PivotTables property of the worksheet class Advance thanks xld03-06-2013, 07:37 AMTry this modification Sub macro() With Sheets("Sheet1") .PivotTables("PivotTable4").ChangePivotCache _ ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Worksheets("Sheet2").UsedRange, _ Version:=xlPivotTableVersion14) .PivotTables("PivotTable4").PivotCache.Refresh End With End Sub abraham3003-06-2013, 09:39 AMThanks Xld for ur kind help Powered by vBulletin Version 4.2.2 Copyright © 2016 vBulletin Solutions, Inc. All rights reserved.