Automatically Updating Filter in Multiple Sheets

I have data in multiple sheets.  Each row of data in each sheet is identified 
with a text identifier.  I can use auto-filter to select the prefered text 
identifier in each sheet so that it shows only that data required.  This is 
slow and tedious. 

I have also tried the Advanced filter referencing a Named cell which 
indicates the indentifier.  However, I still have to update each sheet 
manually.  Is there a way to automatically update the single filter on all 
sheets simply by typing in the text identifier in a Named cell and pressing 
enter? 

Thanks in Advance,

TonyG
0
Tonyg1 (11)
1/11/2005 11:17:01 PM
excel 39879 articles. 2 followers. Follow

1 Replies
390 Views

Similar Articles

[PageSpeed] 29

How about something like this:

Option Explicit
Sub testme()

    Dim myValue As Variant
    Dim wks As Worksheet
    
    myValue = Worksheets("Sheet1").Range("a1").Value
    
    For Each wks In ActiveWorkbook.Worksheets
        With wks
            If .FilterMode Then
                .ShowAllData
            End If
            With .AutoFilter.Range
                .AutoFilter field:=1, Criteria1:=myValue
            End With
        End With
    Next wks
End Sub

I used A1 of Sheet1 for my key value.

And I filtered on the first column in the autofilter range.

(I'd put a button from the Forms toolbar near that key cell and assign it to
this macro.)

TonyG wrote:
> 
> I have data in multiple sheets.  Each row of data in each sheet is identified
> with a text identifier.  I can use auto-filter to select the prefered text
> identifier in each sheet so that it shows only that data required.  This is
> slow and tedious.
> 
> I have also tried the Advanced filter referencing a Named cell which
> indicates the indentifier.  However, I still have to update each sheet
> manually.  Is there a way to automatically update the single filter on all
> sheets simply by typing in the text identifier in a Named cell and pressing
> enter?
> 
> Thanks in Advance,
> 
> TonyG

-- 

Dave Peterson
0
ec357201 (5290)
1/11/2005 11:31:41 PM
Reply:

Similar Artilces:

Shared printers automatically adding themselves
I don't know if this is more appropriate for a SBS, Win XP, Vista, 7, or hardware NG, but I'll start here. We have SBS 2008 using a domain and a mix of clients running XP through 7. We have three network printers plus two USB printers that are shared from their host PCs to the network. My problem is that when I log onto a client and look at the list of printers in control panel I will see - 3 network printers - 3 more copies of the network printers for each client (Brother Printer on PC1, Brother Printer on PC2, etc.) - 2 USB printers on their correct host - 2 more cop...

Find MAX data in sheet (Cell)
How would one go about finding the cell that contains the MAX info, o say any/all cells that exceeds 8,000 characters/spaces in a Excel page -- confuzedagai ----------------------------------------------------------------------- confuzedagain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943 View this thread: http://www.excelforum.com/showthread.php?threadid=49148 1. Highlight the entire document 2. Select Format->Conditional Formatting 3. Select "Formula Is" 4. Input this formula =LEN(A1)>8000 5. Format with something obvious like green bac...

Missing something basic
Working with Access 2003. I have a table of users, their phone numbers, and job classes called tblUsers. A query extracts that information into qryUsers. I have a form that uses the query as the record source called frmUsers. The form only contains a single combobox so someone can select the user for which they wish to view data in other forms. It works fine for the first user on the list in the combobox. Selecting any other user in the combobox causes the first record in the table to change to that user's name. For example: The table and the query both contain: Timmy Bobby ...

Automatic stripping/saving of attachments?
Is there a way on Outlook 2003 to automatically save attachments into a specific directory and to then strip them from the email? I get lots of email of family members and the email will include a couple of pages of text and then normally a load of photos. When I used The Bat! I could set it to save the attachments to a specific directory and it would then strip them from the email, so my mailbox files didn't grow too huge. If ten people send me 1MB of mail, my pst will grow by 10MB whereas in The Bat!, the mailbox folders would only grow by a matter of KB because the files weren't st...

SBS2003 + Internet Explorer
We have a customer who has an SBS 2003 Premium server with ISA 2004 They have just installed a bolt on to Outlook which searches their mailboxes or something. They have discovered that they cannot get this to work unless the untick the 'Use automatic configuration script' in Internet Explorer. Trouble is each time they re-boot a PC the tick comes back, so sounds like it's a policy setting somewhere. What is this ? Where is it configured and what are the implications of turning it off ? Could it be that rather than turn it off whatever it is ought to be con...

Do NOT want Shape Objects to automatically link
In Word 2007, I have been trying to create a document using the Shapes and Text Box objects. Each time I try to insert another shape or Text box it automatically links to the one before. So when I move one the other one moves and if I delete one the other one is also deleted. I have looked through the options but cannot find why this is happening. I have Word 2007 installed on another computer and it does not do that. Any suggestions as to why or better yet, how can I stop this from happening? Thanks Karen This is a form of document corruption. See http://word.mvps.org...

to extract multiple values from an array
Hi, I am into Image processing industry, for each job we create unique code in excel, we Process 20 jobs in a day, I want to list all the job Code in "summary of the day sheet" that we complete for the particular day . On Dec 30, 4:56=A0am, ratan h <ratha...@nextgenalbums.com> wrote: > Hi, > > I am into Image processing industry, for each job we create unique > code in excel, we Process 20 jobs in a day, I want to list all the job > Code in "summary of the day sheet" that we complete for the particular > day . Not enough info but, assuming you are...

Am I able to manage multiple email accounts
I have several email accounts including Gmail, HOtmail and corporate. I can't make Entourage mail (2004) list individual accounts or find how to manage profiles. Apple mail works but doesn't allow me to insert a tiff company logo without making it an attachment. Outlook allows for all of the above.<br> Appreciate help > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285235088_9822952 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/7/08 1...

Filter customerid
Hello, When I choose to sort the sales transaction records by "customerid", in the Sales Transaction Entry screen, I would like to further filter the records based on the starting character of the customerid. I would want to accomplish this using VBA. In other words if the customerid is chosen to be the sortby option, then if I click on the nextbuttontoolbar/previousbuttontoolbar/ endoffilebuttontoolbar etc, I would want to filter the records based on the starting character of the customerid, in addition to the sort by customerid. Thanks ...

how do i search an excel sheet for links to another spreadhseet?
Is there a Find function that allows me to look for links in a sheet? When I can't find links, I'll use Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm Flutie99 wrote: > > Is there a Find function that allows me to look for links in a sheet? -- Dave Peterson ...

Open & update another file (through macro) while running macro
I am using MS Excel 2003 and I need help to solve my problem. One excel file (suppose A.xls) in which I’ll update my graph and the data is available in another file (suppose B.xls). What I am doing (through macro) is that I have created one button (in A.xls) and when I pressed it; its open the file B.xls (the data file) and at their I select month from drop down from which its update the pivot table based on the selection month and then same I was doing with another data excel file (suppose C.xls). Query : Is there any solution that when I pressed button from file A.xls its ...

Bulk Updates
This is a multi-part message in MIME format. ------=_NextPart_000_00D5_01C38666.B1E5CF00 Content-Type: multipart/alternative; boundary="----=_NextPart_001_00D6_01C38666.B1E5CF00" ------=_NextPart_001_00D6_01C38666.B1E5CF00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Guys, How could I update a lot of records at once? (something faster than = updating a single object crmobject.Update(xml)) I know I can go into the DB and find that field but is there a = cleaner way? Cheers, Angus=20 =20 =20 =20...

updating #2
My microsoft money mutual fund page does not update anymore. Is there any reason? I did not change anything Thanks Dada ...

Publisher 2007 and Microsoft Updates
I created a newsletter yesterday - everything fine - use Publisher all the time. Then an automatic update came down this morning and I can't open the file - it says "Publisher cannot open file" Other documents in Publisher are opening - any ideas? http://support.microsoft.com/kb/972566/ -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Kim" <Kim@discussions.microsoft.com> wrote in message news:8754339B-99DC-4D09-83AD-6B34D8215274@microsoft.com... >I created a newsletter yesterday - everythin...

Configure single Outlook 2003 Account to try multiple outgoing servers?
Is there a way to configure a single Outlook 2003 account to try different SMTP servers for Outgoing mail? I log in from two different locations, and both are behind firewalls. And from each location I can't get to the other SMTP server to send my mail. To get around this, I've created two Outlook accounts to use. One for each location. But this is not a good solution, because I have to remember to use the correct account, and it's easy to make a mistake. ....When I do, the outgoing mail very quietly sits in my Outbox, and doesn't get sent. People get very upset at me for ...

Unable to update retirement account
Anyone have a solution to this problem? Whenever I try to update my retirement account I received the error message immediately: "this operation cannot be performed" Chris In microsoft.public.money, Chris wrote: >Anyone have a solution to this problem? Whenever I try to update my >retirement account I received the error message immediately: > >"this operation cannot be performed" See http://support.microsoft.com/kb/902466/en-us Unfortunately, I've run the File Repair utility numerous times and it comes back clean - so this doesn't fix this er...

Flagged message in DRAFTS folder is automatically "complete"
I'm trying to flag messages in my Outlook 2003 Drafts folder aso they show up in "For Follow Up" and I remember to complete them. Unfortuantely the flag is automatically set to complete and cannot be changed in the right click menu Igrayed out). Does anyone have a solution for this? Hmmm... It works here. Are you using a pst or Exchange account? Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http:...

CRM for international companies
Hi, Being an international company, our pricelists are in different currencies. MS CRM stores the currency on the Organization level and not on the Business Unit level, which essentially prevents our use of it. (1) Any suggestions for a way to overcome this problem (besides "translating" the EU, EMEA and US pricelist to one currency, e.g. dollars, or waiting for CRM 2.0) ? (2) I noticed that the database schema contains "Organization ID" for all basic tables. This probably means that multiple organizations can run on the same CRM server. Currently, I can't se...

many sheets from "master data"
Hi guys, In the last few days, ive posted some questions with some success, bu not being able to completely work though my problem. Given that I a novice/intermediate in relation to VBA, I am having extreme difficult augmenting the code to do what i like. Attached is the file so you can visualize what I am talking about. What I want to do is to put each "page" of data on a new slide. As yo can see, all the data is found on the first page. In column T, you ca see where each new page starts (as indicated by the data and pag number). I having trying tirelessly to get the code su...

Compare two Sheets with Conditional Formatting #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Importing and updating Products in MS CRM...
We are speaking to one of our clients on MS CRM implementation. However we are stumped on one issue. They have more than two thousand products and the price of these products changes as frequently as once in a month. Using DMF, we can import the products one off but how about updating the prices at least once in a month. Is there any user friendly utility available for MS CRM that can be used by our client to import Products information on regular basis? May be that someone will suggest SCRIBE but it is too pricey. Any help is appreciated. Hi, Onkar. We had the same situation, and even ...

can't apply Office 2008 1224 update
List members: I regrettably was talked into reinstalling Office 2008 for the Mac on my Intel Mac running OS 10.6.2. Everything is fine except that I can't get Office 2008-1224 Update to apply. My HD appears with a yellow triangle over it, saying the update is inapplicable. I've tried trashing the product ID as Microsoft's web page suggests; no luck. No trial copies of Office anywhere on my HD. Would appreciate any advice; thanks in advance. - Mark Pavlick This is identical to the problem I reported on 25 March (Installing Office 2008). In my case the prob...

Multiple email accounts #11
I use Outlook 2003 with multiple accounts (4 POP accounts and one Exchange account) and I have the following problems: Most of the time I am not connected to the corporate network, so most of the time I have no connection to the Exchange server. Because of this Outlook shows the dissconected icon on the bottom right status bar. Once Outlook says that it is dissconected from the Exchange server it doesn't do the automatic syncs with the POP accounts through the internet (which is indeed available). Is there a way to make Outlook autosync with the POP accounts if it is not conne...

Multiple Exchange boxes in one domain
Hi all, I have the following setup for my domain: HQ: Server 2003 Exchange 2003 We have a branch office located on another industrial estate 1/2 mile away, and we currently connect to them via WiFi (11Mb) and they are in the same domain and subnet as us. I am looking to add a second exchange box at their site so they can get their mail "locally". When I look at setting it up tho ot seems that exchange will still send the messages across the WiFi due to there only being 1 MX record? Is there a way I can get them to recive their mail directly over their own Broadband link? Also ...

Problems with Windows XP updates
Downloaded automatic updates have (4) times caused my computer to not restart. Could not start in safe mode to access restore point. Could not reboot to last known good configuration. Had to repair OS and decided to turn off auto updates. I'm now missing too many updates but it's pointless to download updates - mess up my computer - do repair and have the same thing happen again. Any suggestions? The original Windows XP came installed on my computer, but I did buy a backup CD from Microsoft - this is the CD I've used to do OS repairs. My computer summary is li...