Pivotcache.sourcedata 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
Vba Runtime Error 1004 Pivot Table Field Name Is Not Valid
and policies of this site About Us Learn more about Stack Overflow run time error 1004 reference is not valid pivot table the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation run time error 1004 pivot table field name is not valid Tags Users 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
Run-time Error '1004' Unable To Get The Pivotfields Property Of The Pivottable Class
only takes a minute: Sign up .PivotCache.SourceData causing Application or object defined error up vote 0 down vote favorite I have written the following code to edit the range of a two pivot tables. Up until the line of stars my code functions properly, however I get a "Run-time error '1004': Application-defined or object-defined" error on the .PivotCache.SourceData = rng.Address(True,
Create Pivot Table Vba
True, xlR1C1, True) line. I have no idea what the cause of the error is as I copied the code from above and only changed the sheet name and pivot table name (they both definitely exist in my file). Any help? Dim RowCount As Integer Dim ColCount As Integer Dim rng As Range Dim CurrentPeriod As String Dim PivotList As Variant Dim Piv As String Dim PivotSht As String Dim PivotNme As String RowCount = WorksheetFunction.CountA(Sheets("Data").Range("A:A")) ColCount = WorksheetFunction.CountA(Sheets("Data").Range("1:1")) Set rng = Sheets("Data").Range(Sheets("Data").Cells(1, 1), Sheets("Data").Cells(RowCount, ColCount)) CurrentPeriod = Sheets("Static").Range("CurrentPeriod") With Sheets("Val Cat Current Returns (Adj)").PivotTables("CatCurrentPivot1") .PivotCache.SourceData = rng.Address(True, True, xlR1C1, True) .PivotFields("Period_id").CurrentPage = CurrentPeriod .PivotCache.Refresh End With ********************************** With Sheets("Val Cat Trend Returns (Adj)").PivotTables("CatTrendPivot1") .PivotCache.SourceData = rng.Address(True, True, xlR1C1, True) .PivotCache.Refresh End With excel-vba pivot-table share|improve this question asked Sep 9 '15 at 14:34 user3737057 617 A shot in the dark.. try this... .PivotCache.SourceData = Sheets("Data").Name & "!" & rng.Address(True, True, xlR1C1, True) –Siddharth Rout Sep 9 '15 at 15:45 Thanks for the suggestion, but still getting the same error. –user3737057 Sep 9 '15
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users 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 minute: Sign up Run time error 1004 for pivot table creation up vote 0 down vote favorite I am trying to create a pivot table from a .csv file. But I am having a run http://stackoverflow.com/questions/32482469/pivotcache-sourcedata-causing-application-or-object-defined-error time error 1004: Reference is not valid over at the ActiveWorkbook portion of the code. Any suggestions for this? My code is as follows Sub test() Dim ws As Worksheet Set ws = ActiveSheet Sheets.Add ws.Select ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ ws.Name & "R1C1:R101643C21", Version:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _ , DefaultVersion:=xlPivotTableVersion10 End Sub I did a Sheets.Add to add in new worksheet. I used a ws.Name as the name can be any name. Just an addition question, is it possible http://stackoverflow.com/questions/20365882/run-time-error-1004-for-pivot-table-creation to change R1C1:R101643C21 to a varying range as the data may not be that big or small? excel-vba share|improve this question edited Jan 30 '15 at 3:28 pnuts 33.9k63870 asked Dec 4 '13 at 2:47 user1204868 3215927 1) ws is empty before ws.Select. 2) yes, you can use a dynamic range. you can use another variable to store the range –sam092 Dec 4 '13 at 3:26 @sam092 hi, I left out the Set ws = ActiveSheet in the code posted earlier on. I have it in my macro but it still show the error –user1204868 Dec 4 '13 at 3:31 Yes, it is possible to use a dynamic range. You may be getting an error if you try to create additional tables with the same name and destination? –David Zemens Dec 4 '13 at 3:33 @DavidZemens what do you mean by create additional tables? U mean in my code? –user1204868 Dec 4 '13 at 6:26 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted Notice the ! in SourceData. The missing ! is causing the invalid reference Sub test() Dim ws As Worksheet Set ws = ActiveSheet ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ ws.Name & "!R1C1:R11C2", Version:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _ , DefaultVersion:=xlPivotTableVersion10 End Sub For dynamic range, see this Sub test() Dim ws As Worksheet Dim rng As Range Set ws = ActiveS
Forums Excel Questions Pivotcache sourcedata change 1004 error Results 1 to 1 of 1 Pivotcache sourcedata change 1004 errorThis is a discussion on Pivotcache sourcedata change 1004 http://www.mrexcel.com/forum/excel-questions/612395-pivotcache-sourcedata-change-1004-error.html error within the Excel Questions forums, part of the Question Forums category; I http://www.vbaexpress.com/forum/archive/index.php/t-45512.html would really appreciate any help on this. Have tried various ways to update the pivotcache source data and consistently ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid error 1004 Mode Switch to Threaded Mode Feb 9th, 2012,09:00 PM #1 magnet New Member Join Date Feb 2012 Posts 1 Pivotcache sourcedata change 1004 error I would really appreciate any help on this. Have tried various ways to update the pivotcache source data and consistently get the ]1004 Application or object-defined error' when it gets to the red code where it tries to change the is not valid sourcedata. The various methods I have tried are remmed out. I am running this from MS Access. Thank you for any help. Code: Sub TestPivot() On Error GoTo Err_FormatSpreadsheets_Click Dim db As Database Dim xl As Excel.Application Dim xlw As Excel.Workbook Dim xlsh As Excel.Worksheet Dim xlshp As Excel.Worksheet Dim filenm As String Dim rng As Range Dim pt As PivotTable Dim ptc As PivotCache Dim straddr As String Set db = CodeDb Set xl = CreateObject("Excel.Application") Set xlw = xl.Workbooks.Open("C:\WORKINGFOLDERS\Office\AppTest\EasternPiv.xls") xl.Visible = True Set xlsh = xlw.Worksheets("ProvList") With xlsh .Select .Cells.Select Set rng = xlsh.Range(.Cells(1, 1), xlsh.UsedRange.Cells.SpecialCells(xlLastCell)) 'rng.Name = "PivRangeName" 'MsgBox rng.Address 'MsgBox rng.Address(1, 1, xlR1C1) 'straddr = "'" & xlsh.Name & "'!" & rng.Address(1, 1, xlR1C1) straddr = xlsh.Name & "!" & rng.Address(1, 1, xlR1C1) 'MsgBox straddr Set xlshp = xlw.Worksheets("ProvPT") For Each pt In xlshp.PivotTables 'Sheets(1).PivotTables(1).PivotCache.SourceData = "'" & Sheets(2).Name & "'!" & Range("A5:T40000").Address 'Sheets(1).PivotTables(1).PivotCache.SourceData = Sheets(2).Name & "!" & Range("A5:T40000").Address(ReferenceStyle:=xlR1C1) Set ptc = pt.PivotCache 'ptc.SourceData = Worksheets("ProvList")!Range("A1:C5000").Address(ReferenceStyle:=xlR1C1) 'ptc.SourceData = Worksheets(3).Name & "!" & Range("A1:C5000").Address(ReferenceStyle:=xlR1C1) 'ptc.SourceData = xlsh.UsedRange.Address(1, 1, xlR1C1) 'ptc.SourceData = "'ProvList'!" & Range("$A:$C").Address 'ptc.SourceData = rng.Address(1, 1, xlR1C1) 'ptc.SourceData = rng.CurrentRegion.Address 'xlshp.PivotTables(1).PivotCache.SourceData =
data of sheet2 by simply writing the VBA code. Data of sheet2 are frequently changing. The recorded macro is below. sub macro() 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, _ 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.