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

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