Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 3rd 08, 09:45 AM posted to microsoft.public.excel.programming
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:

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


  #2   Report Post  
Old March 3rd 08, 11:13 AM posted to microsoft.public.excel.programming
Reg Reg is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 48
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trouble with Pivot Tables & Named Ranges PurpleMilk Excel Worksheet Functions 0 March 4th 10 11:41 PM
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... christian_spaceman Excel Programming 3 December 24th 07 01:15 PM
More Assistance Needed with Pivot Tables Michelle Excel Discussion (Misc queries) 1 August 14th 07 11:05 PM
Help needed with pivot tables danielz Excel Discussion (Misc queries) 4 April 5th 06 10:22 AM
Pivot Tables - Named Range dipsy Excel Worksheet Functions 5 August 23rd 05 04:50 PM


All times are GMT +1. The time now is 09:50 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017