+ Reply to Thread
Results 1 to 10 of 10

Copy data using advanced filter across all sheets.

  1. 11-04-2020, 07:03 AM #1
    hsm3005
    hsm3005 is offline
    Registered User
    Join Date
    10-19-2020
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Copy data using advanced filter across all sheets.

    I am trying to create a macro which copies and pastes filtered data from all sheets of another workbook. The below code does the job but lacks accuracy in filtering data.

    Please note the database range is same across all sheets of another workbook (Headers on Row no.7)

    Accuracy Problem:
    It does not meet the criteria and pastes the entire data of sheet1 of the another workbook.
    Only the data of column A is pasted from other sheets, rest all the columns are blank.
    Expectations: The code should search for the given criteria in each sheet of another workbook. If no data found then move to another sheet and paste the entire filtered data in this workbook.

    Could anyone please help solve this?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by hsm3005; 11-04-2020 at 10:23 AM.
    Reply With QuoteRegister To Reply
  2. 11-04-2020, 09:49 AM #2
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data using advanced filter across all sheets.

    It would be more helpful if you share the file with some data as per the yellow banner above.
    Reply With QuoteRegister To Reply
  3. 11-04-2020, 10:23 AM #3
    hsm3005
    hsm3005 is offline
    Registered User
    Join Date
    10-19-2020
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Re: Copy data using advanced filter across all sheets.

    Hi, I have uploaded the sample file for your reference.
    Reply With QuoteRegister To Reply
  4. 11-04-2020, 10:35 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,432

    Re: Copy data using advanced filter across all sheets.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I will do it for you as a courtesy this time: https://www.mrexcel.com/board/thread...heets.1150207/)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 11-04-2020, 08:36 PM #5
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data using advanced filter across all sheets.

    I made a slight change:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 11-05-2020, 12:05 AM #6
    hsm3005
    hsm3005 is offline
    Registered User
    Join Date
    10-19-2020
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Re: Copy data using advanced filter across all sheets.

    Thank you for your reply. Unfortunately the code doesn't work as expected. I could see only the last line of the criteria has been pasted or may be the code overwrites the data. Secondly I wanted it to paste the data from Range.A7 (Headers). Could you please check at you end?
    Reply With QuoteRegister To Reply
  7. 11-05-2020, 12:34 AM #7
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data using advanced filter across all sheets.

    The file is giving me the expected results. I believe the issue is with how the criteria is setup. Review the setup that I have used to see if this method gives you better results.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 11-05-2020, 12:51 AM #8
    hsm3005
    hsm3005 is offline
    Registered User
    Join Date
    10-19-2020
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Re: Copy data using advanced filter across all sheets.

    I tried your file. If I remove any one line of the criteria then the output is not matching the criteria set.
    Reply With QuoteRegister To Reply
  9. 11-05-2020, 01:18 AM #9
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data using advanced filter across all sheets.

    I've made the criteria set dynamic so that changing the criteria lines does not require changing the code. If the criteria contains blank lines all items will be pulled.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 11-05-2020, 02:10 AM #10
    hsm3005
    hsm3005 is offline
    Registered User
    Join Date
    10-19-2020
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Re: Copy data using advanced filter across all sheets.

    This is awesome. Can we restrict the code to not copy the entire sheet and move to another sheet if it doesn't find a match in a anyone of the sheet? This thing would be very helpful as I have more than 100K lines in each sheet.

    Example: Sheet1(No match found) (Do not Copy), Sheet2 (Match found) (Copy), Sheet3(No match found) (Do not Copy), Sheet4 (Match found) (Copy)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to create report using Advanced Filter, multiple filter criteria and Hidden Sheets.
    By MaintWork in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-04-2017, 02:39 AM
  2. Must Advanced Filter be used with Pivot Table Slicers in order to auto copy and paste data
    By LuScannon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-06-2017, 03:55 PM
  3. [SOLVED] Open file dynamically and copy data using advanced filter
    By 4gurus in forum Excel General
    Replies: 4
    Last Post: 09-11-2013, 07:06 AM
  4. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  5. How to use advanced filter simultaneously in few sheets
    By markos17 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2011, 11:58 AM
  6. advanced filter on hidden sheets
    By imatomic in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2009, 10:47 PM
  7. Advanced Filter on Protected Sheets?
    By documike in forum Excel General
    Replies: 1
    Last Post: 01-02-2005, 05:06 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1