help needed - named ranges and pivot tables
The sourcedata argument of the pivotTableWizardMethod is a string - so just
change your code to: SourceData:="PivotArea" "Reg" wrote: Hi I've been mining some data and come across a problem I can't figure out. The data is refreshed using a query on one sheet and then a module carves it up and creates a new sheet which I then want to pivot. The final section (copied below) selects all the refreshed records from the new sheet and declares a name for it. This name is the same was that a pivot table uses that exists on a separate sheet (this is not created by the module, I defined it once and am now trying to force a refresh with the new data). The module fails with a 'reference not valid' when it reached the bit that tells the PT what the sourcedata is now If I then go back to the new worksheet and manually recreate the name, then go to the pivot table wizard and re-enter it everything refreshes as expected. My confusion is that the code section that creates the name seems to work (Insert, Name, Define shows it and the reference is correct) and the pivot table wizard is happy to work with an identical name once it has been manually defined - even though I can't see any difference between the definition the code creates and the manual one i enter. For reference: PivotArea is 'dim'ed as a string and initialised to five chars (eg: FEB29) Countrec is an integer representing the number of rows in the refreshed set. Pressing Debug when the 'Refence not valid' message appears highlights the line shown with below Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Names.Add Name:=PivotArea, RefersToR1C1:=SheetName1 + "!R1C1:R" & countrec & "C32" Selection.AutoFilter Sheets("Pivot").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=PivotArea ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Defect/PCR/Task", "State"), ColumnFields:="Data", PageFields:=Array( _ "uniqueID", "Years", "as_submit_date", "SLC_Phase", "Severity", "MPP Name") Application.CommandBars("Stop Recording").Visible = False Any help or alternative ways of doing things appreciated Reg |
help needed - named ranges and pivot tables
Thanks for the suggestion but PivotArea is defined as a string earlier.
I think the problem was to do with a blank column that got included in the area but instead of giving me the usual complaint about column heading/field names it gave the invalid reference error instead. Oh well. Thanks Again "DomThePom" wrote: The sourcedata argument of the pivotTableWizardMethod is a string - so just change your code to: SourceData:="PivotArea" "Reg" wrote: Hi I've been mining some data and come across a problem I can't figure out. The data is refreshed using a query on one sheet and then a module carves it up and creates a new sheet which I then want to pivot. The final section (copied below) selects all the refreshed records from the new sheet and declares a name for it. This name is the same was that a pivot table uses that exists on a separate sheet (this is not created by the module, I defined it once and am now trying to force a refresh with the new data). The module fails with a 'reference not valid' when it reached the bit that tells the PT what the sourcedata is now If I then go back to the new worksheet and manually recreate the name, then go to the pivot table wizard and re-enter it everything refreshes as expected. My confusion is that the code section that creates the name seems to work (Insert, Name, Define shows it and the reference is correct) and the pivot table wizard is happy to work with an identical name once it has been manually defined - even though I can't see any difference between the definition the code creates and the manual one i enter. For reference: PivotArea is 'dim'ed as a string and initialised to five chars (eg: FEB29) Countrec is an integer representing the number of rows in the refreshed set. Pressing Debug when the 'Refence not valid' message appears highlights the line shown with below Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Names.Add Name:=PivotArea, RefersToR1C1:=SheetName1 + "!R1C1:R" & countrec & "C32" Selection.AutoFilter Sheets("Pivot").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=PivotArea ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Defect/PCR/Task", "State"), ColumnFields:="Data", PageFields:=Array( _ "uniqueID", "Years", "as_submit_date", "SLC_Phase", "Severity", "MPP Name") Application.CommandBars("Stop Recording").Visible = False Any help or alternative ways of doing things appreciated Reg |
All times are GMT +1. The time now is 12:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com