Change Pivot Table Page Value

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 found the below code which looks like it may be close to what I need but 
my lack of VB knowledge meant I could not adapt it -

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("SelDept").Address Then
    Me.PivotTables(1).PivotCache.Refresh
End If
End Sub

Help please...
0
Utf
5/19/2010 12:27:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
729 Views

Similar Articles

[PageSpeed] 0

On May 19, 5:27=A0am, kernel <ker...@discussions.microsoft.com> 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 situa=
tion
> means I still need to ask for help.
>
> Scenario:
>
> Worksheet a =3D "Scorecard"
> FieldName =3D "CustomerNumber"
>
> Worksheet b =3D "Products Resume"
> PivotTable =3D "PivotTable2"
> PivotTable Page Field =3D "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 p=
ivot
> table.
>
> I found the below code which looks like it may be close to what I need bu=
t
> my lack of VB knowledge meant I could not adapt it -
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address =3D Range("SelDept").Address Then
> =A0 =A0 Me.PivotTables(1).PivotCache.Refresh
> End If
> End Sub
>
> Help please...


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address =3D Range("SelDept").Address Then
    Me.PivotTables(1).PivotFields("Account
Number").CurrentPage=3DRange("SelDept").Value
    Me.PivotTables(1).PivotCache.Refresh
End If
End Sub

I have expected that Range("SelDept") avlue will decide the page of
pivottable.


0
Javed
5/19/2010 7:34:43 AM
Thanks for the response. Based on your suggestion and with my fields added 
the code looks like this -

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/21/2010 2:27:01 AM
Reply:

Similar Artilces:

Want use results of Drop down list to look up a value
I am using Excel 2002 and I have a multisheet workbook and on the first sheet I have created a series of 6 drop down lists pulling from predefined named ranges. I also have limited subsequent choices based on what is selected in the other lists. The end result is the user will be selecting parameters that identify a product and now I am stuck with an easy way to look up the associated part number based on the parameters they selected. Essentially, once they have chosen the parameters in the list, I would like to create a macro that uses those values to locate the associated part...

Changing Resource and fields on a subform
I have a subform with fields on it that are present when i first open the form. But when I click on a status button, I'd like the subform to change the fields that are present. For instance when i first open the fields are formid, lastname, firstname, subject, date, range But when i click on formstatus button, i'd like the fields to show: formid, subject, range, product I know how to use recordsource to change the recordsource, but then the fields that I am not using show up as #name? How do I get the subform to change field names so that the fields I am not using, doesn't show ...

Paste links, changed filename
I have created 3 exel 97 Files. Each File has approx 30 sheets. One of them is a source file. The other 2 use file1 data using paste link(paste special). At some stage I have changed the names of the files and now when I open them it asks the usual question " do you want to update linked information", Iclick "yes" ~ then the computer wants me to select the source file. Before, it just started straight into the file I wanted ~ how can i resolve this( with as little effort as possible)? Try hitting no, then do Edit / Links / Change Source, save the workbook and hopef...

"Change Credit Details" button does not appear
I would like to update my interest rates and credit limits monthly, but the "Change Credit Details" button does not appear when I go under the accounts' settings. In microsoft.public.money, mar11974 wrote: >I would like to update my interest rates and credit limits monthly, but the >"Change Credit Details" button does not appear when I go under the accounts' >settings. See if http://support.microsoft.com/kb/281109/en-us helps. I've no idea if it helped the original poster, but it seems I have the same issue, and it didn't help me. I'm ...

Most logical value
I would like to know if there is a method of determining "the most logical" value in a table. These are some data that I put in the table below. These numbers are obtained from some dedicated software to compute the influence of an investment on some projects. I would like to fill in the gaps myself. I can add the data from column 10 en 20 and divide it by 2 to compute the value for column 15, but is there a better method? investment year 0 5 10 15 20 25 30 2005 72,8 72,8 72,8 72,8 72,8 72,8 72,8 2006 79,4 76,7 71,5 60,9 50,3 2007 92,3 87,4 76,9 55,6 33,5 2008 108,6 102,3 8...

Show which cell has MAX, MIN values?
At the bottom of a couple thousand rows of data, I have =MAX and =MIN formulas. Is there some way I could make the cells beneath my MAX and MIN formulas show me the address of which cell has the displayed MAX or MIN value? At least the row number? Ed Ed, To return the row =MATCH(cell with Max or Min value,range starting in row 1,false) or to return the address, say, in Cell N3000, for a value given in N2999 =ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1)) or to return other matching information, like a name in column A =INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE)) HTH, Bernie MS E...

100% Stacked Bar chart, two bars, want percent as well as real value
I am attempting to chart the following: Series 1 - Values over the prior 30 days Series 2 - Values over the prior 12 months I would like to show both data series as 100% bar charts, so users of the information can compare percentage of the last 30 days with percentage of the same piece of data over the last 12 months. So, for example the two data series might look like this: Series 1 20 25 45 65 Series 2 211 324 403 822 I want to display both the actual value, and the percentage of the total, for each series, in the labels. For some reason, showing percentage is not an option. Why is th...

can i change the read receipt
This is a multi-part message in MIME format. ------=_NextPart_000_0108_01C6AD7D.3E5D58A0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable The read receipt by default set by outlook can be changed if yes pls. = let me know how ??? ITDuke ------=_NextPart_000_0108_01C6AD7D.3E5D58A0 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"te...

Pivot table and it's macro
I am creating a table and from the table a pivot table using a macro. When I run the macro the table gets created without a problem but when it goes to create the pivot table I get an error and the debugger highlights this section: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Table8", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _ :="Perf Tables!R2C8", TableName:="PivotTable1", DefaultVersion:= _ xlPivotTableVersion12 Can anyone see where the problem is happening? My ...

Change height of row
Excel seems to limit the height of the row to some presets. Is there a way to set height of row to any height? Am trying to make spreadsheet line up with a pre-printed form. Hi i think the limit is 1 pixel -- Regards Frank Kabel Frankfurt, Germany "Pam MacT" <Pam MacT@discussions.microsoft.com> schrieb im Newsbeitrag news:CCAE9073-80C5-47B1-B103-7834241AF34B@microsoft.com... > Excel seems to limit the height of the row to some presets. Is there a > way > to set height of row to any height? Am trying to make spreadsheet line up > with a pre-printed form. ...

how to change business address
I can't seem to figure out for the life of me WHERE OR HOW to change my business address so my NEW address prints on my invoices now that I have moved. Can anyone help me with this? PLEASE????? LGLEIM In microsoft.public.money, LGleim wrote: > can't seem to figure out for the life of me WHERE OR HOW to change my >business address so my NEW address prints on my invoices now that I have >moved. Can anyone help me with this? PLEASE????? Is there a Tools->Settings->BusinessInformation place? YES!!! You did it! Thank you so much! "Cal Learner-- MVP" wrote: ...

Changing info from one worksheet to the next
Hi all. I have linked my 55 sheets to one which is great. What i need to know now if possible. Each sheet has the same question over 11 columns Each row is dated and a numeric number from 1 - 10 in each row Now on the master sheet where everything is linked, is there a way that If i changed the date on the master sheet it would reflect the answers from the row with that date? At present the answers showing is for 01/03/10, but i would like to look at the totals for 08/03/10 and show the answers from each sheet for that date. I could have a sheet for each week, but im hoping there i...

Printing from a Master page
I am trying to print a document and the text area is overprinted in black. Anyone know how to correct that? Might think about a printer driver update. Is this only happening on one publication? What version Publisher? Not sure I understand what is happening. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "sherlynn" <sherLynn@discussions.microsoft.com> wrote in message news:6169A241-2AE6-4DD7-A932-E5432A55C777@microsoft.com... >I am trying to print a document and the text area is overprinted in black. > Anyone...

deleting pages in publisher 2002
When deleting p.5 (or any page) of my 19 page document, last page deletes instead! What's happening here? How can I delete my page? I'm using "edit" delete page. Also, I am not in Master Page mode. K. On Thu, 23 Feb 2006 22:39:35 +0000, KLeDrew wrote (in article <CA87F080-CAF5-4A38-956C-FDF8896CA92A@microsoft.com>): > When deleting p.5 (or any page) of my 19 page document, last page deletes > instead! What's happening here? How can I delete my page? I'm using "edit" > delete page. Also, I am not in Master Page mode. > > ...

Changing name from Arabic to English
I, m registering all new recruitment employees so always I'm writing the employee name by Arabic and English. So there is any code to change automatically the person name for Arabic to English ...

How do you turn off "Save a copy/Overwrite changes" dialog box
Hello Guys, I have created a (password to open) workbook using Excel 2003 SP1 on a shared network drive which when I try to save by using Ctrl S or clicking the Save icon gives me an irritating dialog box which asks: "The file 'filename.xls' may have been changed by another user since you last saved it. In that case what do you want to do?" -Save a copy -Overwrite changes Please can someone tell me how to deactivate this annoying dialog box as I have been unsuccesful so far in my efforts :confused: Many thanks in advance Nick -- Nicko -----------------------------...

Pivot Table % of
I have a pivot table like the followng: Joe Trans Ct A 5 B 3 C 2 Total Joe 10 Kelly A 6 B 1 C 4 Total Kelly 11 I added another Count of Trans shown as a "% of column", but it gives me the percent of the entire column when what I really want is the % of Total Joe, % of Total Kelly, etc. In this instance I simply made the names go across instead of down which solved the problem, but there are instances where I don't want to do that. Any suggestions for getting the percent per item rather than pe...

Pivot table row header
Hi, In a pivot table, I have 2 fields in the row section. How can I have the value on the first fields to repeat in every row instead of just showing once? Thanks!! Try Debra Dalgleish's page at: http://www.contextures.com/xlDataEntry02.html Fill Blank Cells Fill Blank Cells Programmatically* [*Sub FillColBlanks() 'by Dave Peterson 2004-01-06] -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Jason" <Jason@discussions.microsoft.com> wrote in message news:08549CBC-68A7-442E-BABF-D48B11E29479@microsoft.co...

Pivot Table Updates Issue
My Data had a coloum containing stuff like Marketing Department Sales Department Shipping Department I built a bunch of pivot tables on this data. Then I changed the data to read: Marketing Dept Sales Dept Shipping Dept and performed a Refresh Data. Now, on the filter pulldowns on any of the Pivot tables, I see Marketing Department Sales Department Shipping Department Marketing Dept Sales Dept Shipping Dept The first three option no longer exist in any of the data, and I do not want them in the list. How to fix this problem without recreating and reformatting 38 seperate Pivot table...

How do I change mailbox owner in AD?
How do I change a mailbox owner in AD? Can it be done? T. The mailbox is linked to the user account. You can grant an additional user(s) "owner" access by granting Full Mailbox access on the Exchange Advanced Tab of the user properties. Glen "Thomas Houseman" <thomashouseman@hotmail.com> wrote in message news:OZwMptUxEHA.2192@TK2MSFTNGP14.phx.gbl... > How do I change a mailbox owner in AD? Can it be done? > > T. > > Verify that you have mailbox retention enabled. ( right-click mailbox store/properties/limits). This is extremely Important! ...

Excel pivot tbl
Brand new laptop w/Windows XP. Has Office 97 installed (first), than complete install of Office XP(second). Than install of PTSfull.exe (pivot table services that come w/ sql server analysis services service pack 2). Go to data manu - PivotTable and Pivot Chart report - External data source/Pivot table - Get Data - OLAP cubes - new data source - select an olap provider for the database you want to access - blank dropdown (no Microsoft OLEDB Provider for OLAP Services or Microsoft OLEDB Provider for OLAP Services 8.0). Tried removing Office 97 & Office XP than installing PTS - sa...

Excel macros page breaks but not on row 1 and column value in footer
Hi, I have seen some posts that are similar, but no one seems to have the problem that I am having. I will post my macro in this, for anyone that is interested. My users get a csv file every month, and we have to clean it up. This macro does that. My last issues are this: 1) having the spreadsheet create page breaks whenever the value in column B changes. Below is just that code. Code: col = 2 LastRw = ActiveSheet.UsedRange.Rows.Count For X = 2 To LastRw If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1") Then ActiveWindow.SelectedSheets.HPageBreaks...

Permission for account change
Dear Friends I have my domain as abc.com I have another domain xyz.com for which i have created a separate account for the users so that they can send the email with both the domain by using from filed. i have one user user1 who is facing problem i have created userxyz for with the default email address of xyz.com domain but i am facing strange problem as the permission of sending email is reseting. Kindly suggest what can be the problem. best regards Niitmalad What version of Exchange and how/where exactly it is resetting? Niitmalad wrote: > Dear Friends > > I have...

in what order should i place multiple brochure pages?
i am wondering in what order should i lay out my pages in my brochure so it will print correctly . . . I would use one of the brochure templates, create, print it out and edit to my liking. -- Don Vancouver, USA "critic" <critic@discussions.microsoft.com> wrote in message news:BB2EA24A-EC9F-44EB-AB02-209FF66A1F77@microsoft.com... >i am wondering in what order should i lay out my pages in my brochure so it > will print correctly . . . ...

change the size of Font for reading incoming mail
hi, buddies. I'd like to change the Font with incoming email in my outlook2003. my operation is in the following. click options on the Tools menu, and then click Mail Setup tab, click Fonts. So I change the Font in this items:"When composing and reading plain text" but I can't find the font to take any change when I read incoming email. What my question is how I can change the Font size in the incoming email. thanks is the incoming mail plain text and the change isn't working or is the incoming mail html? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://ww...