Linking 3 auto filters to a pivot table on another worksheet

Hi all,

Can anyone help?

I have used the code below to successfully link 3 auto filters to a
pivot table on another worksheet.

1. Area (Sales area)
2. MA (Market area)
3. Name (Centre name)

When I select values using each of the 3 filters it works perfectly
i.e. the pivot table changes to show the data from my selection. The
problem I have is that I want the filters to work independently e.g.
if I select only the area filter and the other filters remain un
selected, I want the pivot table to show all of the data for the
selected area only.
I assume I need to use an IF statement to say if a filter is not used
then show "(ALL)" default aut filter value?

The code I am using is as follows:

Private Sub Worksheet_Activate()
    Dim rCell As Range, strArea As String
    Dim strMA As String, StrName As String

    On Error Resume Next
    For Each rCell In Blad1.Range("A4:C4")
        Select Case UCase(rCell)
        Case "AREA"
            strArea = rCell.End(xlDown)
        Case "MA"
            strMA = rCell.End(xlDown)
          Case "NAME"
            StrName = rCell.End(xlDown)

        Case Else
        End Select
    Next rCell

    With Me.PivotTables("PivotTable1")
        .PivotFields("Area").CurrentPage = strArea
        .PivotFields("MA").CurrentPage = strMA
        .PivotFields("Name").CurrentPage = StrName

    End With
    On Error GoTo 0
End Sub
0
daynereed (2)
10/1/2008 3:25:55 PM
excel 39879 articles. 2 followers. Follow

2 Replies
263 Views

Similar Articles

[PageSpeed] 46

Hi,

How about setting the other AutoFilters to All.  But regardless you will 
need to set all three filters because Excel will never know when to use one 
and when to use all three.

-- 
Thanks,
Shane Devenshire


"daynereed@googlemail.com" wrote:

> Hi all,
> 
> Can anyone help?
> 
> I have used the code below to successfully link 3 auto filters to a
> pivot table on another worksheet.
> 
> 1. Area (Sales area)
> 2. MA (Market area)
> 3. Name (Centre name)
> 
> When I select values using each of the 3 filters it works perfectly
> i.e. the pivot table changes to show the data from my selection. The
> problem I have is that I want the filters to work independently e.g.
> if I select only the area filter and the other filters remain un
> selected, I want the pivot table to show all of the data for the
> selected area only.
> I assume I need to use an IF statement to say if a filter is not used
> then show "(ALL)" default aut filter value?
> 
> The code I am using is as follows:
> 
> Private Sub Worksheet_Activate()
>     Dim rCell As Range, strArea As String
>     Dim strMA As String, StrName As String
> 
>     On Error Resume Next
>     For Each rCell In Blad1.Range("A4:C4")
>         Select Case UCase(rCell)
>         Case "AREA"
>             strArea = rCell.End(xlDown)
>         Case "MA"
>             strMA = rCell.End(xlDown)
>           Case "NAME"
>             StrName = rCell.End(xlDown)
> 
>         Case Else
>         End Select
>     Next rCell
> 
>     With Me.PivotTables("PivotTable1")
>         .PivotFields("Area").CurrentPage = strArea
>         .PivotFields("MA").CurrentPage = strMA
>         .PivotFields("Name").CurrentPage = StrName
> 
>     End With
>     On Error GoTo 0
> End Sub
> 
0
10/1/2008 11:31:00 PM
On 2 Oct, 00:31, ShaneDevenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> How about setting the other AutoFilters to All. =A0But regardless you wil=
l
> need to set all three filters because Excel will never know when to use o=
ne
> and when to use all three.
>
> --
> Thanks,
> Shane Devenshire
>
>
>
> "dayner...@googlemail.com" wrote:
> > Hi all,
>
> > Can anyone help?
>
> > I have used the code below to successfully link 3 auto filters to a
> > pivot table on another worksheet.
>
> > 1. Area (Sales area)
> > 2. MA (Market area)
> > 3. Name (Centre name)
>
> > When I select values using each of the 3 filters it works perfectly
> > i.e. the pivot table changes to show the data from my selection. The
> > problem I have is that I want the filters to work independently e.g.
> > if I select only the area filter and the other filters remain un
> > selected, I want the pivot table to show all of the data for the
> > selected area only.
> > I assume I need to use an IF statement to say if a filter is not used
> > then show "(ALL)" default aut filter value?
>
> > The code I am using is as follows:
>
> > Private Sub Worksheet_Activate()
> > =A0 =A0 Dim rCell As Range, strArea As String
> > =A0 =A0 Dim strMA As String, StrName As String
>
> > =A0 =A0 On Error Resume Next
> > =A0 =A0 For Each rCell In Blad1.Range("A4:C4")
> > =A0 =A0 =A0 =A0 Select Case UCase(rCell)
> > =A0 =A0 =A0 =A0 Case "AREA"
> > =A0 =A0 =A0 =A0 =A0 =A0 strArea =3D rCell.End(xlDown)
> > =A0 =A0 =A0 =A0 Case "MA"
> > =A0 =A0 =A0 =A0 =A0 =A0 strMA =3D rCell.End(xlDown)
> > =A0 =A0 =A0 =A0 =A0 Case "NAME"
> > =A0 =A0 =A0 =A0 =A0 =A0 StrName =3D rCell.End(xlDown)
>
> > =A0 =A0 =A0 =A0 Case Else
> > =A0 =A0 =A0 =A0 End Select
> > =A0 =A0 Next rCell
>
> > =A0 =A0 With Me.PivotTables("PivotTable1")
> > =A0 =A0 =A0 =A0 .PivotFields("Area").CurrentPage =3D strArea
> > =A0 =A0 =A0 =A0 .PivotFields("MA").CurrentPage =3D strMA
> > =A0 =A0 =A0 =A0 .PivotFields("Name").CurrentPage =3D StrName
>
> > =A0 =A0 End With
> > =A0 =A0 On Error GoTo 0
> > End Sub- Hide quoted text -
>
> - Show quoted text -

Thanks Shane. I guess the two formats are incompatible

Chris
0
daynereed (2)
10/2/2008 8:10:41 AM
Reply:

Similar Artilces:

Pivot table not valid if the database is opening too.
Hi All, I have a pivot table which is connected to a database through Microsoft Access Driver.On the table option, the option " save my password " is unchecked.And the problem is : the pivot table is not valid when the database is opening too. Although I have inputted the password of the admin user of the database for the authorization,it still invalid.How to solve this problem. ...

Move PF From 2000 to 2003 #3
We are running EX2000 SP3 and just built an EX2003 SP2 server for migrating all public folders to. In the past (5.5 to 2000), we setup replication for all folders and then remove the old one after they in-sync. This process take very long time. Now, we heard there is a new tool on EX2003 SP2 "Move all Replicas", is that something to replace the old process? How does it work on large data? Will it verify the folders before removing the old data? Our PF currently 160GB in size and we use EFORMS and Event agent a lot. Does the "Move All Replicas" also handle the exchange syste...

Mail Filtering
I have SMTP mail filtering configured and turned on for my SMTP virtual server IP address. My question is will the sender of a message that is going to be filtered out get a non-delivery response or will it just look as if the message has gone through to them? ...

import from one deploment to another
Hi all, We have 2 different crm 3.0 deployments. I want to import 25000 incidents from one deployment to another one. Both deployments use the same incident forms and both have the same customer accounts created. Is there an easy way to to this. Thanks. You have comingled data there which would not fit the redeployment wizard. My hunch is to export the data to Excel files merge the files together and then do some processing on duplicate records and fields. Once you have that down, import the data back into CRM. Or import the combined data into another SQL database and use TSQL command...

discount on item only if sold with another specific item
I have created a custom add-in that applies a 25% discount to items that are in the 'Accessory' department, but only if they are being sold with an item from the 'Phones' department. If there is no phone sold on the transaction it recalculates the accessory price as the regular retail price. This works fine, except for one problem. In order for this to work correctly when testing it, the cashiers all have to have the access to pricing privilege turned on, which means that they will then have the ability to override prices manually. We do not want this. Is there a wa...

Auto email insertion and submission
Hello. I'm trying to set outlook to automatically insert a text file into an email and send it. I would need to do this once and outlook will be doing it on daily basis. Is that possible? can you help me ? ...

Link to existing document, or create new from template
Hello, I have very little Access and Coding experience. I'm trying to create a simple database to let me track documents my office creates about certain events. For each event, we create an Excel file from a template, and a Word document from a template, then we send it for review to certain other offices. For my form coding question: On the form, once I get to the field "Word Document" I'd like Access to determine if a Word document is already attached to this record's specific primary key number. If not, open up a new one from the template (let's say c:\Word...

11.3.3 Office Update
I get this error message - "the installer could not locate the correct version of the software to install this update. See the read me file included with this installer to determine if your software meets the requirements for this update" To my eye I meet all the system reqs - 10.4.8 / Office v.X. Have restarted to no avail. What gives? Thanks, Steve Office v.X? The 11.3.3 update is for Office 2004 only; you'll need to get the 10.1.9 update instead. On 31/1/07 3:40, in article 1170211227.299934.52380@s48g2000cws.googlegroups.com, "sstepanek2@comcast.net" <...

Creating a filter for a list box
I want to create a filter for a list box that I can turn off and on using a check box on the same form. Currently, I am using a query to filter the list box. The query serves as the data source for the box. Is there a way I can create a filter and then turn it on and off using a check box? Thank you for your help. Best, John Use criteria in the query like this --- Like IIf([forms]![myform]![mycheck]=-1,"*" & "s" & "*") & "*" The above only select items that have an 's' if box is checked. -- KARL DEWEY Build a little - Test a lit...

Grand Total on Pivot Table turned on but no totals showing
I am trying to display Grand Totals for Rows in my Pivot table. I have selected the Grand Total for Rows option but to no avail. Any ideas would be appreciated Thanks -- GaryRitchie ------------------------------------------------------------------------ GaryRitchie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24331 View this thread: http://www.excelforum.com/showthread.php?threadid=379317 ...

Filtering???
Help!!! I am trying to do a bulk fax using mail merge... I have brought in my contact information from Outlook to Excel. I have several people at the same company and I only want to do 1 fa cover to each company, but have 2 or 3 peoples names on the cover. I know that there must be a way to merge this so that I don't get a individual fax cover for each person. Can anyone help me????:confused: Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

2-Column Layout with auto text flow?
I would like to have a worksheet that has a 2-column "newspaper" format, where text flows off the bottom of the first column to the top of the second column. Is this possible using Excel2000? As a very last resort, I'll use a macro to split the text and put it into different cells. But this is messy and not very accurate. My wish is to somehow link cells together and have the text flow between them, or use a control which will do this. Thanks! -- Matt Kruse http://www.JavascriptToolbox.com why dont you use Word? "Matt Kruse" <newsgroups@mattkruse.com> wr...

Forms - Call Data from Another Table
Hi I'm used to using lookups and forms etc but need to do something that I think links queries, VBA (completely new to) and forms: I have a form for creating new applicants I want to build a form that allows the user to search a contacts table on FirstName, MiddleName, Surname; receive a list of matches and then either 1. Select one of the matches which would then populate the applicants table or 2. Reject the matches and create a new record as normal Can you anyone point me in the right direction? Thanks James Sounds like you are asking how to do callbacks. Open a sub form ...

macro to copy Vlookup formula to some cells with a filter on
Hello, I was wondering if somebody could help me with this macro… I have some data with a filter on, so that only the rows with blank cells are showing. I want to enter a VLOOKUP in the cell, and copy it down to all the blank cells. I tried just recording a macro, but when I run the macro it doesn’t work. Any ideas of how to do it? Example: 1) Before the filter: Colums A -B - C A x x - 1st Row A - 2nd Row A x x - 3rd Row A - 4th row A x x - 5th row 2) After the fil...

Add new records into source table on Form
I created a form for training records. The data source is table TRAINING. The form has Combo box that is used to select employee. When the employee is selected, the employee's data, such as employee_id, manager, phone, training_course display in the form. This part of the form works well. There was new request from my client. It's requsted that when the training_course displayed on the form is changed, the record with new training_course can be added into source table TRAINING. So, I created "Add" buttorn on the form and used append query method to add the recor...

Pivot Table Layout Help
I have a Pivot Table that has two row fields listed in the layout. So when you run or finish the Pivot Table it displays the row field horizontally across the spreadsheet. I want to take them and displa them vertically down the left side and can't remember how this is done You can't just hoover over the field and drag it over there because i then just moves the first field over to the left. I know this can b done because I have performed this a long time ago and forgot how. Please help... :eek -- mardma ----------------------------------------------------------------------- mardman&#...

table based i one field splitted in 2 fields
Hi everyone, I have a txt file with product EAN and label quantity to print. 3564700010822 2 3564700010983 5 3564700422687 10 I have a form to print label quantity. What i pretend is "convert" the table in a 2 fields table or query like: ProductCode |Quantity 3564700010822|2 3564700010983|5 3564700422687|10 Any help is apreciated. -- Thanks, Sorry if my english isn''t correct, but I''m from Potugal ;) Emanuel Violante Galeano On Thu, 29 Apr 2010 07:27:01 -0700, Emanuel Violante <EmanuelViolante@discussions.microsoft.com>...

Unhide macro worksheet
I have created a macro in my personal.xls worksheet. I want to delete the macro but am getting a message that I need to unhide the worksheet. I cannot locate the worksheet where the macro is stored and cannot find the command to unhide it. Help!!! Thank you very much, -- JoeF hi the command to unhide the sheet.... 2003 on the menu bar>format>sheet>unhide 2007 Home tab>cells group>Hide&hide>unhide sheet regards FSt1 "JoeF" wrote: > I have created a macro in my personal.xls worksheet. I want to delete the > macro but am get...

Pivot Table Duplicate January
I have a column in my original data that uses the formula =TEXT(D11,"mmmm") to generate the month name, so that I can filter my pivot table by month. For some reason the filter option on my pivot table gives 3 separate January's and only one of those actually shows data. The other two show nothing. ( this only accurs with January, none of the other months are affected) It is no problem for me, but other people will be using this pivot table to generate reports and I want to remove the duplicates to eliminate any confusion. Can anyone help? Salahan I have had t...

Junk Mail Filter Ignores Setting to Ignore Contact List Members
I find that the Outlook 2003 Junk Mail filter ignores my Safe Sender list in some cases. I have several lists and users that I get mail I want from but most mail from them go straight into the junk e-mail folder instead of being ignored by outlook as they should. Same applies to some people on my contact list, i've set the junk mail filter to ignore email from people on my contact list but it doesn't help. It appears that certain words and/or short messages triggers the junk mail filter but then it bypasses the "ignore people on my contact list" and "safe send...

Link Form to Access Database thru DSN
I am using Front Page 2000 running on Windows 7 What I am trying to figure out is how to create a .ASP page to connect to my Access database I uploaded to my web server provider. At their web service I used their "Manage DSN Records" feature to upload my database and create a link. Then inside the Front Page program I am at the "Insert > Database > Results Wizard trying to make the connection. I'm guessing in here I need to enter the Domain Name, DSN Name, Etc. I have tried every possible combination and just get a error message when I click on verify th...

can excel check one column against another
Hi. I have a spreadsheet with columns such as A, B, C, D, filled with numbers. Each row across such as row 1, then row 2, then row 3, etc of numbers is totaled in column E. Countless rows, each of which is totaled per row and then ends with a grand total at bottom of column E. Next comes columns F, G, H, also filled with numbers (but different numbers), which is totaled per row in Column I. At the bottom of Column I is a grand total. Even though arrived at using different sets of numbers, -both grand totals - in columns E and I must match. Sometimes they don't match. Ouch. So here is m...

how to copy row heights from 1 worksheet to another
In Excel, if I copy and paste information from one work sheet to another I can copy column widths by using Paste Special - how can I copy row heights? If you copy the whole row, you can paste special the format--which includes the rowheight (along with the rest of the cells, too. It might do more than you want. sadie wrote: > > In Excel, if I copy and paste information from one work sheet to another I > can copy column widths by using Paste Special - how can I copy row heights? -- Dave Peterson ...

Worksheets #4
Hi, 1. Excel is not letting me add any/delete/rename any worksheets in my workbook. Is there a setting somewhere I need to change to allow me to do this? I currently have 15 worksheets. Ben, Your workbook may be protected. Click on Tools, Protection to find out. Dan. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Ben, Your workbook may be protected. Click on Tools, Protection to find out. Dan. ------------------------------------------------ ~~ Mess...

another: CAPS, can you preset a column (States?)
can you preset a column to display it's contents in all caps? More specifically, I have a column where users enter in state abbreivations (i.e., GA or TX); does anyone have a useful, easy way to do this? Thank-you!!! Jacob, One possible way would be to add the desired results to the Auto Correct feature under the Tools menu. Charlie O'Neill "jacob farino" <jfarino@mindspring.com> wrote in message news:0wYFc.7757$yy1.2545@newsread2.news.atl.earthlink.net... > can you preset a column to display it's contents in all caps? More > specifically, I have a colu...