Runtime Error'1004': Method 'range' Of Object'_global'failed 2

[kingz2000]

Hi,

I get this message:

"Runtime Error'1004': Method 'range' Of Object'_global'failed "

when I run through the last bit of the code below , which is supposed to merely add a page item to my pivot.

Dim s As Worksheet
Dim p As PivotTable
Dim i As PivotItem

For Each s In Sheets
For Each p In s.PivotTables
Debug.Print "Refreshing Pivottable " & p.Name & " on sheet " & s.Name

p.PivotFields("[Contract Dimension].[Legal Entity]").AddPageItem ( _
"[Contract Dimension].[Legal Entity].&[" & Range("LegalEntity") & "]") ', True


can anyone help,please?

Thanks in advance!

 

[combo]

The 'LegalEntity' named range should be in an active workbook if you use 'Range' without preceding object. Is it so?

combo

 

[kingz2000]

I have a "parameter" sheet, where I have written my pivot field ,on A5, and the selected field ,on B5 of the prarameter sheet.
All I want is for the Pivot table to take the parameters into account. Should it maybe read:

p.PivotFields("[Contract Dimension].[Legal Entity]").AddPageItem _
"[Contract Dimension].[Legal Entity].&[" & Range(Workbook.Worksheet("Parameter").Range(B5)) & "]", True

The thing is, I have several sheets with pivots, and I want to merely enter a pivotfield and its value on the parameter sheet, and find it updated on all the pivot tables in the preceding sheets.

 

[kingz2000]

Or maybe I'm ging the wrong way about this problem. Let me repeat my problem elaborated.

I have about 10 Pivot tables on seperate sheets. The all use the same pivot fields. I'd like to simply select a parameter field and its value on a "parameter" sheet, and witness all the 10 sheets being updated with the selection.

Now looking above, am I going about it the wrong way?

Thanks in advance

 

[combo]

While testing, add message to the loop:
MsgBox Range("LegalEntity")

To access a cell in the worksheet, you can use:
ThisWorkbook.Worksheets("Parameter").Range(B5)
if the sheet 'Parameter' and code are in the same workbook,
otherwise:
Workbooks(WorkbookName).Worksheets("Parameter").Range(B5)
For named range:
WorkbookReference.Worksheets("Parameter").Range("LegalEntity")

combo

 

[SkipVought]

hi,

            p.PivotFields("[Contract Dimension].[Legal Entity]").AddPageItem ( _
                "[Contract Dimension].[Legal Entity].&[" & [b]YourParameterSheetObject.[/b]Range("LegalEntity") & "]") ', True

In addition, use the Watch Window to determine that "[Contract Dimension].[Legal Entity]", for instance, has the value that you expect, as well as "[Contract Dimension].[Legal Entity].&[" & YourParameterSheetObject.Range("LegalEntity") & "]"

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

For Each s In Sheets

is this legit?


unknown

 

[SkipVought]

Yes, as you can see, there are only 2 ;-)
[tt]
sheets
[/tt]
But it would be more rigerous to code...

for each s in thisworkbook.sheets

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

So Sheets has an implicit ThisWorkbook parent?


unknown

 

[PHV]

So Sheets has an implicit ThisWorkbook parent?
In fact, the implicit parent is Application.ActiveWorkbook

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

That makes sense. I am trying to think of an equivalent object in Word, but can not.

Documents(name) can be used, but its parent is Application ("up" one...so to speak). Application.Documents

Application.ActiveWorkbook.Sheets seems "up" two.

Dim oBm As Bookmark
For Each oBM In Bookmarks

is legitimate, but again, its parent is ActiveDocument, with ActiveDocument parent being Application.

So, technically, is the implicit parent of Bookmarks Application, or ActiveDocument?

Application.ActiveDocument.Bookmarks

Although, I am so ingrained in trying to fully qualify, I doubt I will stop using

For Each oBM In ActiveDocument.Bookmarks

simply because I often am doing actions between documents, so I need to be explicit.


unknown

 

[xlbo]

To be more rigorous yet, should use:

for each s in thisworkbook.worksheets

As otherwise will also include any chartsheets which have very few common properties with worksheets

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

rigor more 'tis! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

where's the groan emoticon?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Oh, are you awake DOWN there?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

It's midday over here Skip - must be still last night over there!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question