+ Reply to Thread
Results 1 to 4 of 4

Excel vba - copy an advanced filter range then transpose function

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003
    Posts
    39

    Excel vba - copy an advanced filter range then transpose function

    Hi,
    I would like to select a specific range/column in the output range of an advanced filter function, and then copy/paste special the results from the query to the next empty row in another worksheet using transpose function. I gather that the array size to copy will change each time a new query is run on the data, and that the range to copy to/paste special/transpose would change, and that these need to be the same size in order for the function to work. Any suggestions for VBA code for the advanced filter/select data/edit/copy/paste special/transpose function?

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: Excel vba - copy an advanced filter range then transpose function

    Perhaps something like this?

    Please Login or Register  to view this content.
    Where the B column is the filtered column youu wish to copy from.

    Alf

  3. #3
    Registered User
    Join Date
    04-10-2013
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Excel vba - copy an advanced filter range then transpose function

    Hi, thanks for this, I tried this code, all I got was the cells highlighted with a dashed line, and the usual message 'select destination & press ENTER, or choose PASTE'.
    Many thanks,
    Jonathan

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: Excel vba - copy an advanced filter range then transpose function

    The macro does what you asked for i.e.

    advanced filter/select data/edit/copy/paste special/transpose function?
    Just add the

    Please Login or Register  to view this content.
    after the "PasteSpecial" line to get rid of marching "ants" and "select dest" message,

    and if you do wish to get rid of the autofilter as well use

    Please Login or Register  to view this content.
    Alf

+ 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. Advanced filter with function as criteria range?
    By dakke in forum Excel General
    Replies: 23
    Last Post: 06-10-2021, 04:48 PM
  2. [SOLVED] Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2021, 04:30 PM
  3. Transpose function - advanced
    By Damian84 in forum Excel General
    Replies: 2
    Last Post: 03-15-2012, 09:33 AM
  4. Excel 2010 Advanced Function Filter problems - 2007 issues
    By adamwestrop in forum Excel General
    Replies: 3
    Last Post: 07-29-2011, 01:52 PM
  5. advanced filter a range:Advanced Filter function
    By Il Principe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 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