Pivot Table Page Field value from cell in another worksheet

I have looked through the other posts on the subject and the combination of 
my low skills in VB and the other solutions not exactly covering my situation 
means I still need to ask for help.

Scenario:

Worksheet a = "Scorecard"
FieldName = "CustomerNumber"

Worksheet b = "Products Resume"
PivotTable = "PivotTable2"
PivotTable Page Field = "Account Number"

When CustomerNumber value on worksheet "Scorecard" is changed I need the 
Pivot Table Page field "Account Number" to use this value to filter the pivot 
table.

I have received some help for this problem and got this far -

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("CustomerNumber").Address Then
    Me.PivotTables(2).PivotFields("Account Number").CurrentPage = 
Range("CustomerNumber").Value
    Me.PivotTables(2).PivotCache.Refresh
End If
End Sub

.....but I get the error

"method 'PivotTables' of object '_Worksheet' failed

Any ideas?
0
Utf
5/24/2010 2:42:06 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1461 Views

Similar Articles

[PageSpeed] 19

Hi

How many Pivot Tables do you have on worksheet b?
If there is only 1, then change the code to
PivotTables(1)
in both instances within the code

Alternatively, be specific and use
PivotTables("PivotTable2")
in both instances
--
Regards
Roger Govier

kernel wrote:
> I have looked through the other posts on the subject and the combination of 
> my low skills in VB and the other solutions not exactly covering my situation 
> means I still need to ask for help.
> 
> Scenario:
> 
> Worksheet a = "Scorecard"
> FieldName = "CustomerNumber"
> 
> Worksheet b = "Products Resume"
> PivotTable = "PivotTable2"
> PivotTable Page Field = "Account Number"
> 
> When CustomerNumber value on worksheet "Scorecard" is changed I need the 
> Pivot Table Page field "Account Number" to use this value to filter the pivot 
> table.
> 
> I have received some help for this problem and got this far -
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = Range("CustomerNumber").Address Then
>     Me.PivotTables(2).PivotFields("Account Number").CurrentPage = 
> Range("CustomerNumber").Value
>     Me.PivotTables(2).PivotCache.Refresh
> End If
> End Sub
> 
> ....but I get the error
> 
> "method 'PivotTables' of object '_Worksheet' failed
> 
> Any ideas?
0
Roger
5/24/2010 8:47:25 AM
Hi,

Thanks for the response. I changed the code to -

 Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = Range("CustomerNumber").Address Then
     Me.PivotTables("PivotTable2").PivotFields("Account Number").CurrentPage 
= Range("CustomerNumber").Value
     Me.PivotTables("PivotTable2").PivotCache.Refresh
  End If
 End Sub

.....but I am still getting the "method 'PivotTables' of object '_Worksheet' 
failed" error.



"Roger Govier" wrote:

> Hi
> 
> How many Pivot Tables do you have on worksheet b?
> If there is only 1, then change the code to
> PivotTables(1)
> in both instances within the code
> 
> Alternatively, be specific and use
> PivotTables("PivotTable2")
> in both instances
> --
> Regards
> Roger Govier
> 
> kernel wrote:
> > I have looked through the other posts on the subject and the combination of 
> > my low skills in VB and the other solutions not exactly covering my situation 
> > means I still need to ask for help.
> > 
> > Scenario:
> > 
> > Worksheet a = "Scorecard"
> > FieldName = "CustomerNumber"
> > 
> > Worksheet b = "Products Resume"
> > PivotTable = "PivotTable2"
> > PivotTable Page Field = "Account Number"
> > 
> > When CustomerNumber value on worksheet "Scorecard" is changed I need the 
> > Pivot Table Page field "Account Number" to use this value to filter the pivot 
> > table.
> > 
> > I have received some help for this problem and got this far -
> > 
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address = Range("CustomerNumber").Address Then
> >     Me.PivotTables(2).PivotFields("Account Number").CurrentPage = 
> > Range("CustomerNumber").Value
> >     Me.PivotTables(2).PivotCache.Refresh
> > End If
> > End Sub
> > 
> > ....but I get the error
> > 
> > "method 'PivotTables' of object '_Worksheet' failed
> > 
> > Any ideas?
> .
> 
0
Utf
5/24/2010 10:30:01 PM
Hi

If you want to mail me a copy of the workbook, I will take a look.
Mail to
roger at technology4u dot co dot uk
Change the at to @ and dots to . and remove spaces, to make a valid 
eamil address
--
Regards
Roger Govier

kernel wrote:
> Hi,
> 
> Thanks for the response. I changed the code to -
> 
>  Private Sub Worksheet_Change(ByVal Target As Range)
> 
>   If Target.Address = Range("CustomerNumber").Address Then
>      Me.PivotTables("PivotTable2").PivotFields("Account Number").CurrentPage 
> = Range("CustomerNumber").Value
>      Me.PivotTables("PivotTable2").PivotCache.Refresh
>   End If
>  End Sub
> 
> ....but I am still getting the "method 'PivotTables' of object '_Worksheet' 
> failed" error.
> 
> 
> 
> "Roger Govier" wrote:
> 
>> Hi
>>
>> How many Pivot Tables do you have on worksheet b?
>> If there is only 1, then change the code to
>> PivotTables(1)
>> in both instances within the code
>>
>> Alternatively, be specific and use
>> PivotTables("PivotTable2")
>> in both instances
>> --
>> Regards
>> Roger Govier
>>
>> kernel wrote:
>>> I have looked through the other posts on the subject and the combination of 
>>> my low skills in VB and the other solutions not exactly covering my situation 
>>> means I still need to ask for help.
>>>
>>> Scenario:
>>>
>>> Worksheet a = "Scorecard"
>>> FieldName = "CustomerNumber"
>>>
>>> Worksheet b = "Products Resume"
>>> PivotTable = "PivotTable2"
>>> PivotTable Page Field = "Account Number"
>>>
>>> When CustomerNumber value on worksheet "Scorecard" is changed I need the 
>>> Pivot Table Page field "Account Number" to use this value to filter the pivot 
>>> table.
>>>
>>> I have received some help for this problem and got this far -
>>>
>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>> If Target.Address = Range("CustomerNumber").Address Then
>>>     Me.PivotTables(2).PivotFields("Account Number").CurrentPage = 
>>> Range("CustomerNumber").Value
>>>     Me.PivotTables(2).PivotCache.Refresh
>>> End If
>>> End Sub
>>>
>>> ....but I get the error
>>>
>>> "method 'PivotTables' of object '_Worksheet' failed
>>>
>>> Any ideas?
>> .
>>
0
Roger
5/26/2010 4:54:38 PM
Reply:

Similar Artilces:

How to Filter cells and save the file with certain criteria?
Hello. I have a Excel file that contains a large list of Tracking Numbers. The tracking numbers are from two sets of order types- Internet orders and Mail Orders. Orders that are from the Internet are matched up with a order number such as "5678". Orders from the mail-order side are designated by initials "MO". So in Excel it'll look something like this: trackingnumber orderid 123365656666 5467 152155896345 5468 123365634567 MO 152134567789 5469 152151234563 MO Is there a way to eliminate the Mail-Order rows and save the Excel file to ...

NOTEINDX in Batch Header Table
THere is a noteindex field in sy00500, pm00400, pm10100 tables. does any one know what is the purpose of this field? I am getting an error while importing data on these tables. everytime i run the import it says "Null value is not allowed" in this field eventhough i am exporting value for this field. It didnt happen with my other clients. is it ok if we didnt update this field in these tables during integration? Hi Bab The NoteIndex field is used to link to the SY_Record_Notes_MSTR(SY03900) to allow record level notes to be stored against the Batch Header This is no NoteInde...

Table format
Hi When I run the code below I get "External table is not in the expected format" error at line rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText Option Explicit Public Sub SelectFromAccess() Dim rsData As ADODB.Recordset Dim sPath As String Dim sConnect As String Dim sSQL As String 'Clear the destination worksheet Sheets(1).UsedRange.Clear 'Get the database path (same as this workbook) sPath = ThisWorkbook.Path If Right$(sPath, 1) <> "\" Then sPath = sPath & "\" 'Create the connection s...

Dynamics 7.0 tables in SQL server 2000
I need to know what should be the content of each of the tables with names starting with "GL" in SQL Server 2000 when using 7.0. Is there a reference that I can go to in Customersource that tells me this information? Thanks. -- Dave Christman System Developer There is an online reference: Tools >> Resource Descriptions >> Tables. Also there is a SDK avaailable on the Great Plains CD's. "Dave Christman" wrote: > I need to know what should be the content of each of the tables with names > starting with "GL" in SQL Server 2000 when...

modify the Type of Field in a Table From another MDB through a Command button
Hello to all! I hope can help me. By technical questions that would be to me very long to explain, the following necessity arises to me: I need To modify the Type of Field in a Table From another MDB through a Command button. The field at issue would happen to be "Number" to "TEXT". Is this possible one? I wait for alternatives. Thanks in advance, and sorry for my poor english. Dreamer. - Hi Is it not possible to open the other database and simply open the table in design view and then change the field. It seems very complex to do the codeing what will most pro...

pasting into merged cells #2
Every time I try to copy and paste data into a merged cell I get error messages about the cells not being the same size. Even if I try to just paste values only. Is there any workaround or fix for this??? Dennis Try pasting into the formula bar. It's a pain, but it works. HTH Carole >-----Original Message----- >Every time I try to copy and paste data into a merged cell I get error >messages about the cells not being the same size. Even if I try to just >paste values only. Is there any workaround or fix for this??? > >Dennis > > >. > See my res...

Tables/ queries question
I have a table for recording college attendances, some of the courses are at Campus 1 and some are held at Campus 2. I've got a field for course code, course anme and a check box to show if the student is at campus 1. When I enter the student ID number into the form, at the moment the name of the course comes up automatically in the text book, as I think it's reading the information from another database on the system. At the moment I've got a check box on the Form to enter manually into the table whether the student is at Campus 1 or Campus 2. What I wondered is if...

reverse page numbers
I am trying to lay out a booklet in English and Hebrew, and would like it to open and read from right to left. Is there an easy way to number the pages in the opposite way a regular booklet in English would be? ...

Filter by Field in Subform
I need to filter a main form based on fields in my subform. For example, my main form has a field [Order Number] and the header contains standard information about the origin of the parts and what is to be done with them, like the PO Number, and Customer information. My subform contains information like the Part Number, Qty, the ID Number for that particular line item, etc. My boss needs to be able to tell how many orders contain Part Number so and so... information that is specific to the underlying table in the subform, but he also needs the associated information in the main form...

How do I protect my comments from being edited by another user?
I regularly send excel worksheets to a co-worker via e-mail and then she sends it out to others within the company (sales force). There are occasions when she will need to revise it and send it back to me. How do I protect my comments so that they cannot be edited, or can I? I received a worksheet back today that the comments I had entered were changed. These comments are my back-up and I do not want them changed. Can anybody help? highlight the whole sheet format cells / protection / uncheck locked highlight the cells you want protected format cells / protection / check locked tool...

cell protection to allow GROUP/UNGROUP function
2 asthetic questions for all you advanced Excel Users.... Q1. is it possible to protect a worksheet, but still be able to us the +GROUP & -UN GROUP buttons ? to expand / contract the viewe ranges? Q2. anyone know how to change the colour of the AUTO FILTER button - i changes from black to dark blue when activated - i would like to hav this with more contrast and so it jumps out at you ( and the othe plebs in the office who keep forgetting) Regards David ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages dire...

Highlight Cells with different values
Hi, I have a column that I want to locate the differences. I can use the F5---Special---Column Differences. But I wanted to know if I can do it with Conditional Formatting. Like if Value in A1 is different than the values in Column A:A, then it is highlighted... Any help appreciated Fuad Sounds like an Excel question..try one of the Excel newsgroups, this one is for Publisher. "Fuad" <Fuad@discussions.microsoft.com> wrote in message news:0536FB95-362E-412F-BD1C-99BF9B10E8CD@microsoft.com... | Hi, | | I have a column that I want to locate the differences. I can use th...

Reverse Page Numbering
Hi, I'm not sure this can be done, but ... going to ask anyway. XP Pro SP3 and Office 2002. Is there a way to reverse the order of pages in a Word document? Last page becomes first, next to last becomes next, and so on. Instead of 1, 2, 3, .... the physical appearance changes to 42, 41, 40 ... 3, 2, 1. I have a Word document which is displayed 'upside down': In other words, the LAST page is page 1, next to last is page 2, and so on down to the first page, which is page 42, but at the bottom of the file; last page shown. This is meant to be an onscreen refere...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Making cards --2 to a 8 1/2 x 11 page
I want to make cards to send out for memorials, etc. I want 2 cards to a page, top folded 5 1/2 x 8 1/2. What happened to the old Picture It! Publisher 2001? Do you want to make these cards in Publisher? If you do, look at page setup, folded card, tent card, width 4.25, height 5.5. Publisher will say it will print two pages per sheet -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "A Holt" <A Holt@discussions.microsoft.com> wrote in message news:F5F75EEF-B186-4960-9AEB-AF1C678492B7@microsoft.com... >I want t...

problem with pivot table
Hello all: I am trying to make a pivot table from my date. My data includes Names, Dates (which I formatted to month, "m" in the format cells option)and Amount due. In designing the pt, I have "Name" in the row field, "Date" in the columnn field and "Amount due" in the Data field. I want my data to be grouped as follows: Month 1 2 3 4 Name $100 $20 $30 $20 Instead I am getting more than 1 month column i.e. Month 1 1 2 2 2 3 Name $80 $20 $30 $20 Do you know why...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

Payroll w/h tables
When can we expect new payroll withholding tables for Ohio? In December our gov announced a late year change repealing a prior payroll credit previously announced. We have been told that the withholding tables are not correct yet GP/Microsoft has not released new tables yet. When can these be expected since we are practically done with January now??? Thanks. According the information I have, there are no pending changes for OH. You may need to contact Support about this issue. -- Charles Allen, MVP "INC" wrote: > When can we expect new payroll withh...

Cannot insert explicit value for identity column in table x
Sql Server 2005 9.00.4035. We have a hub and spoke topology which includes 6 tables with identity columns. I need to add a new subscription to a new server (same version/sp). I took backup from another subscriber and restored to new server, ran snapshot, push subscription. To make sure a new record would replicate, I added a record at the publisher with the followig text: Insert into tblicimagefolder (imagefolder) values ('dummy') A conflict occurs with the following: This failure can be caused by a constraint violation. Cannot insert explicit value for identity c...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

How do I protect one single cell?
I have a spreadsheet where people are going to type in numbers, and the final cell is a total, which of course I don't want people to accidentally type over, as it will lose the formula behind it. So...... I put some "data validation" behind that cell, to say only allow entries of text length = 50 - a kind of "mock" condition. Nobody'e ever gonna type in 50 chars. are they?! And sure enough, typing in (eg) 854221 brings up the error box to stop them, just like (eg) HHJSYT brings up the error box to stop them. I even unchecked the ignore blank" box, so that ent...

Looking Up Values
I have a database used to record results of laboratory tests, the tests are standard but the priced paid by the customer can vary. Currently I have a standard price included as a field in the Test Table but this is difficult when prices increase across the board as I have to close down the existing test and re-input it with the new cost, also I have to have a routine whereby a form is used to manually enter any alternative prices before a routine is run to invoice the customer. I would like to create some method whereby Access would look for the current price charged to that customer i...

Using Emit to Return a Value
Hello: I am trying to write a piece of code that can implement an interface's methods simply by returning a specified value. I am trying to see how Mock libraries are written. For instance, I have a piece of code that looks like this: MockFactory.CreateMock<ISomething>().Setup(something => something.Foo()).Returns("abc"); What I want to be able to do is somehow implement ISomething.Foo so that it returns "abc". However, I am having a hard time figuring out the Emit code for taking an arbitrary object and incorporating it. I've never want...

Problem with VBA and assignment of a value
I have a form w/subform and need to display the average of one field (AverageAge) and the count of records in the subform on the form somewhere else than in the subform. I calculate AverageAge as Average([Age] in the form footer. I get the record count using the recordCount property o the subform. The problem is that I can't sem to get the value 'AverageAge' into an unbound textbox. Me.AvgAge = Me.Team_Members_subform.Form.AverageAge produces the error Application-Defined or Object-Defined error. Me.AvgAge = Me.Team_Members_subform.Form.Age gives me the correct age f...

A Dynamic Query Worksheet?
I have the unfortunate task of converting a SQL Server database into stand-alone Excel file that will store all the data, as well as act a a "front-end' for browsing the data. I'd like to have an opening page of many different hyperlinks whic would send the user to a second worksheet and also pass a variable. The second worksheet would list the results of a query that would us the variable as a parameter and would get its data from a huge table o a 3rd Spreadsheet. Is this idea possible? If so, I'd appreciate any ideas, tips, hints o how to get this done. Thanks -- Messa...