+ Reply to Thread
Results 1 to 2 of 2

Excel 2007-Adv Filter Copy and Loop with Nested Autofilter Using Values from Adv Filter

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2003
    Posts
    33

    Excel 2007-Adv Filter Copy and Loop with Nested Autofilter Using Values from Adv Filter

    I have a source sheet (“Truck”) from which I copy the unique values to target sheet (“TruckFiltered”). Excel then uses a loop to loop through each individual value in (“TruckFiltered”).Range(“H1”) and run an autofilter to filter out the last update from the source table (1 row returned) and then copy-destination copies the value in the adjacent column in the source table to the value that the autofilter has been run on, (“TruckFiltered”).Range(“H1”). So the auofilter should only be run once on each value in the unique list in column “A” in table (“TruckFiltered”) which it gets from (“TruckFiltered”).Range(“H1”) on each iteration of the loop.

    The problem is that the autofilter is running multiple times on the same value despite the value in (“TruckFiltered”).Range(“H1”) changing on each iteration of the loop (values in “Red” on source and target sheets). Also the returned value for the first iteration of the lookp is not copies to sheet (“TruckFiltered”). The end result have no copied value for the first iteration of the loop and I have multiple values returned from the autofilter and copied to the column adjacent to the unique list which the loop runs through instead of getting the single lastest update value for each unique value in sheet (“TruckFiltered”).
    I’ve struggled with this issue for days now. Your assistance would be greatly appreciated...thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Excel 2007-Adv Filter Copy and Loop with Nested Autofilter Using Values from Adv Filte

    Hi, Southfish,

    the given code will copy all found values over, not as per your request only the latest one.

    Please try:
    Please Login or Register  to view this content.
    If you really have only one cell you want to pass you could assign the value directly instead of using Copy which would bring over formatting as well.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Use a userform to pass values from combo box to autofilter with custom filter for dates
    By Megatronixs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 10:30 AM
  2. Replies: 5
    Last Post: 05-23-2012, 10:33 AM
  3. Can I filter a list using cell values instead of autofilter?
    By janschepens in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2011, 09:28 AM
  4. 2007 PivotTable and filter on multiple values
    By xxxyyyy in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-22-2011, 11:38 PM
  5. Advanced Filter or Autofilter to filter numbers?
    By odeno in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-08-2007, 12:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1