View Single Post
  #1   Report Post  
Old March 3rd 08, 09:45 AM posted to microsoft.public.excel.programming
DomThePom DomThePom is offline
external usenet poster
First recorded activity by ExcelBanter: Mar 2008
Posts: 54
Default help needed - named ranges and pivot tables

The sourcedata argument of the pivotTableWizardMethod is a string - so just
change your code to:


"Reg" wrote:


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

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(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=PivotArea, RefersToR1C1:=SheetName1 +
"!R1C1:R" & countrec & "C32"



ActiveSheet.PivotTableWizard SourceType:=xlDatabase,


ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
RowFields:=Array("Defect/PCR/Task", "State"), ColumnFields:="Data",
PageFields:=Array( _
"uniqueID", "Years", "as_submit_date", "SLC_Phase", "Severity", "MPP
Application.CommandBars("Stop Recording").Visible = False

Any help or alternative ways of doing things appreciated