Activesheet.pivottables Pivottable1 .pivotcache.refresh Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and run time error 1004 pivot table field name is not valid policies of this site About Us Learn more about Stack Overflow the company
Excel Vba Pivot Table Refresh Error 1004
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users refreshtable method of pivottable class failed excel 2010 Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a runtime error 1004 pivot table refresh minute: Sign up Run-time error 1004 for Refresh Pivot Table up vote 0 down vote favorite I created macro which would refresh all pivot tables in a worksheet. The same code works on one sheet however doesn't on the other. I tried to run it in debug mode and it works fine for first few tables and crush on one in
Run Time Error 1004 Reference Is Not Valid Pivot Table
the middle with error: Run-time error '1004': RefreshTable method of PivotTable class failed. There are 20 Pivot Tables in this sheet, but on the one where it works are 26. Your help would be much appreciated. Sub Refresh_Pivots2() Dim PL As PivotTable Worksheets("Incidents Pivots").Activate For Each PL In ActiveSheet.PivotTables PL.RefreshTable Next PL End Sub I just tried another code Sub Refresh_Incidents() Worksheets("Incidents Pivots").Activate Dim A A = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20") For i = 0 To 19 ActiveSheet.PivotTables(A(i)).RefreshTable Next i End Sub if I start from i=0 it gives an error but when switch it to i=1 it works. Any ideas what is wrong with this table. I am able to refresh it manually. excel vba excel-vba refresh pivot-table share|improve this question edited Dec 29 '15 at 19:59 Piotr Leniartek 8791726 asked Dec 29 '15 at 10:42 Paulina Leniartek 115 Can you refresh them all manually? –Rory Dec 29 '15 at 12:06 Hi Rory, yes I can refresh each of this table manually
Forums Excel Questions Pivot Table Error Page 1 of 2 12 Last Jump to page: Results 1 to 10 of 15 Pivot Table ErrorThis is a discussion on Pivot Table Error within the Excel Questions forums, vba runtime error 1004 pivot table field name is not valid part of the Question Forums category; ... LinkBack LinkBack URL About LinkBacks Bookmark unable to get the pivottables property of the worksheet class & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to
Run Time Error 1004 Application Defined Or Object Defined Error
Hybrid Mode Switch to Threaded Mode Aug 9th, 2004,04:08 PM #1 Anthony G. Board Regular Join Date Mar 2002 Posts 465 Pivot Table Error Hello to All… Upon opening an existing file, I receive the http://stackoverflow.com/questions/34509313/run-time-error-1004-for-refresh-pivot-table following error: Unable to Read File….I then click OK, Enable the macros and upon doing so, the following message appears: Excel Table Report on [Sales] Sheet1 was discarded due to integrity problems. I have two Pivot tables on the report – Pivot8 and Pivot1 – two separate worksheets but using the same source data. I have placed a button on each worksheet to enable the user to refresh the Pivot Table. Pivot1 http://www.mrexcel.com/forum/excel-questions/97217-pivot-table-error.html is the table that is being discarded and when I click the button on Sheet1, the following Run time error 1004 appears: Unable to get the Pivot Tables property of the Worksheet class….when I select Debug, here’s the code with the bolded section being the point of error: ActiveSheet.Unprotect Password:="test" Range("K9").Select ActiveCell.FormulaR1C1 = "=NOW()" Range("K9").Select Selection.Copy Range("K9").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("h161").Select ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh ActiveWorkbook.ShowPivotTableFieldList = False Columns("I:I").Select Selection.ColumnWidth = 14.86 Range("A19").Select ActiveSheet.Protect Password:="test" End Sub I guess I understand why I’m getting the Run time error (because the PT no longer exists); question is, why is it being discarded in the first place…?!? Any assistance in correcting this will be greatly appreciated. Thank you…Anthony Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Aug 10th, 2004,09:55 AM #2 Anthony G. Board Regular Join Date Mar 2002 Posts 465 Re: Pivot Table Error I've searched high and low for a reason as to why this can be happening and came up empty handed. Anyone have any suggestions? Thanks...Anthony Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Aug 11th, 2004,05:31 AM #3 BrianB Board Regular Join Date Feb 2003 Location Luton, England. Posts 8,123 Re: Pivot Table Error Try :- Code: ActiveSheet.PivotTables("PivotTable1").RefreshTable S
data of sheet2 by simply writing https://community.spiceworks.com/topic/534015-general-odbc-error-1004 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.
Practice Advantage to Thomson's Practice CS Déploiement SCCM Set up a MediTech Install Replace current EMR product with MediTech. IN THIS DISCUSSION Join the Community! Creating your account only takes a few minutes. Join Now Hello All, I'm having an issue with a pretty old Excel file which pulls data from our manufacturing system to create pick-lists. From last friday, when we open the spreadsheet we get an error saying:- 'ODBC Text driver, too few parameters, expected 1' The sheet then fails to refresh and any macros that perform various refreshes also fail spectacularly with the 1004 General ODBC Error. I've restored a previous version of the file and even tried re-recording the macros but no joy. If i click on debug i get the text below and the line that contains C19 highlighted in yellow (as if to pinpoint the problem). Any ideas where to go next? Mike End Sub Sub Sheet_Refresh_Remove() ' ' Sheet_Refresh_Remove Macro ' Sheets("Live Jobs with Bookings").Select Range("A2").Select Selection.QueryTable.Refresh BackgroundQuery:=False Range("R11").Select ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh Sheets("Production 2").Select Range("C19").Select Selection.QueryTable.Refresh BackgroundQuery:=False Sheets("Running Order").Select Application.Run "production_New_3m.xlsm!Module3.Pivot_Clear_Deleted_Data" ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh Sheets("Komori Schedule").Select Application.Run "production_New_3m.xlsm!Module3.Pivot_Clear_Deleted_Data" ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh Sheets("Plain Punch Pick List").Select Application.Run "production_New_3m.xlsm!Module3.Pivot_Clear_Deleted_Data" ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh Sheets("Komori Pick List").Select Application.Run "production_New_3m.xlsm!Module3.Pivot_Clear_Deleted_Data" ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh Sheets("Refresh").Select Range("B1").Select ActiveWorkbook.Save ' Reply Subscribe RELATED TOPICS: ODBC Error 3146 & 3155 ODBC under WINE error ODBC error & MS Access   4 Replies Tabasco OP Ohiobearsfan Jul 8, 2014 at 12:54 UTC Usually when I have these type of issues, I'm out of a license or a connection by the same name is hung that I need to clear. 0 Sonora OP Mike Marsh GNE Jul 8, 2014 at 12:59 UTC hmm, how would i find out what needs to be cleared? 0 Anaheim OP ShannonVaughan Jul 8, 2014 at 1:00 UTC Without seeing more, it is going to be hard