+ Reply to Thread
Results 1 to 19 of 19

Filter Specific data and copy paste to new worksheet

  1. #1
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    111

    Post Filter Specific data and copy paste to new worksheet

    Hi

    seeking someone help to create VBA code.

    I have large data in one excel file, i want to filter specific data from that then i need to copy paste this to new sheet.

    The specific data is not fixed, will change every time , so i need to select while running the macro by range.
    From Active workbook, active cell column to be filtered (Row and column is not specific) then i need to select the data which to be filtered by selection of range. then the range information to be filtered on active cell column and then copy pasted to new worksheet.

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    If I'm not mistaken to get what you mean, it is something like this :
    1. Sheet2 column B starts from row 8 to row whatever > row 8 contains the criteria
    2. Sheet1 column E need to be filtered with the criteria/s from point-1
    3. After already filtered, copy the whole filtered data to Sheet Output starts from cell B4.

    My way is like this :
    Please Login or Register  to view this content.
    2020-05-20_17-27-26.gif

    The code is assuming that your data table is just like I saw in your Sheet1 workbook, it has no blank row in between.
    And also the criteria range in Sheet2 column B has no blank row as I saw in your Sheet2 workbook.
    Last edited by karmapala; 05-20-2020 at 05:36 AM.

  3. #3
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    Hi karmapala

    Thanks for your help. The code is working fine on sample date. Can you please help to make small the below changes.

    Sheet2 column B: This is not fixed one, can you provide option to select the rows (selection of Range)


    ("B5", .Range("B5")= This is also not fixed one, some times i need to select Column C, can you change to modify the code to "Selection of Column) or VB input of Column name and row number (Ex B5)

  4. #4
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    Quote Originally Posted by SARAN89 View Post
    Can you please help to make small the below changes.
    I will try my best.

    Sheet2 column B: This is not fixed one,
    can you provide option to select the rows (selection of Range)
    Do you mean that the criteria will be based on the user's selection on Sheet2 ?
    (so it doesn't matter on what column/row where the criteria text are)


    ("B5", .Range("B5")= This is also not fixed one,
    some times i need to select Column C, can you change to modify the code to "Selection of Column)
    or VB input of Column name and row number (Ex B5)
    I am sorry I didn't get what you mean, Saran89.

    The line :
    Set TblRng = .Range("B5", .Range("B5").End(xlToRight).End(xlDown).Offset(0, 1))
    is for setting the table range.

    Do you mean that the starting table range is never fixed, so for example there is a possibility
    that the table range starts from cell B5 to G100 ... but at another situation
    the table range starts from cell A1 to D200 ... and another situation it might happen
    where the table range starts from cell K4 to Z300, and so on.


    some times i need to select Column C
    Or do you mean that the column to be filtered is not fixed ?
    Sometimes maybe the criteria text from Sheet2 are in Sheet1 column E (as this is the example in your workbook)...
    but sometimes maybe the criteria text from Sheet2 are in Sheet1 column C, etc.

    So, for example, you will select column C to be filtered out,
    because the text criteria in Sheet2 are all dates which resides in Sheet1 column C.
    That's why the column to be filtered is column C in Sheet1, not column D.

    So, the column to be filtered is never fixed.
    It might be whatever column of the table range in Sheet1 to be filtered.
    Last edited by karmapala; 05-20-2020 at 06:07 AM.

  5. #5
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    Do you mean that the criteria will be based on the user's selection on Sheet2 ?
    (so it doesn't matter on what column/row where the criteria text are)

    Yes by User Selection within work book its not fixed Sheet2

    Do you mean that the starting table range is never fixed, so for example there is a possibility
    that the table range starts from cell B5 to G100 ... but at another situation
    the table range starts from cell A1 to D200 ... and another situation it might happen
    where the table range starts from cell K4 to Z300, and so on.

    Yes you are correct
    that's why i am asking to select column. if you provide VB input box i will give input of ex B5, A1, K4, means this will be me header to make filter.



  6. #6
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    Quote Originally Posted by SARAN89 View Post
    Do you mean that the criteria will be based on the user's selection on Sheet2 ?
    (so it doesn't matter on what column/row where the criteria text are)

    Yes by User Selection within work book its not fixed Sheet2
    So,
    A. it's possible the text contains the criteria are in another Sheet ya ? Not just Sheet2 ?
    Or
    B. do you mean that the Sheet where the criteria text resides is fixed,
    but the name of the sheet is definitely not Sheet2 ?



    Yes you are correct that's why i am asking to select column.
    if you provide VB input box i will give input of ex B5, A1, K4, means this will be me header to make filter.
    Ok... so, to the end-left from user selection in Sheet1 ---> this is the starting column of the table range.
    And to the end-right from the user selection in Sheet1 ---> this is the end column of the table range.

    For example :
    the user choose cell H20 ...
    So, cell H20 is the header to be filtered...
    And so the starting table range column could be anything,
    maybe it's cell A20 or cell B20 or C20 or D20 or E20 or F20 or even H20 itself.
    And the last column of the table range also could be anything, maybe it's cell I20, J20, K20, or even maybe cell AA20.

    Am I correct, Saran ?

    Please try this :
    Please Login or Register  to view this content.
    The code is using a helper column in Sheet 2, which is column AA.
    So please make sure that there is no data at all in column AA Sheet2.

    The code assumes that the text of the criterias will be in Sheet2,
    so before the code ask the user to select the criteria, the code activate Sheet2.
    After the user select the criteria range, it copies the range to cell AA1 in Sheet2.

    Then the code activate Sheet1, then ask the user to select the HEADER CELL to be filtered.
    Based on the user selection, the code locate the starting column and the ending column of the table range.
    The code will fail if there is a column in your table header which has no value.
    The code also will fail if in the last column of the table range - there is a row with no value.
    Last edited by karmapala; 05-20-2020 at 07:28 AM.

  7. #7
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    Hi

    it's possible the text contains the criteria are in another Sheet ya ? Not just Sheet2 ?

    Yes the critiriea will be in any worksheet on the same workbook. Its not just sheet2.


    And Sheet1 also not specific. it will be active worksheet.


    For example :
    the user choose cell H20 ...
    So, cell H20 is the header to be filtered...
    And so the starting table range column could be anything,
    maybe it's cell A20 or cell B20 or C20 or D20 or E20 or F20 or even H20 itself.
    And the last column of the table range also could be anything, maybe it's cell I20, J20, K20, or even maybe cell AA20.

    Yes you are correct

  8. #8
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    Quote Originally Posted by SARAN89 View Post
    Hi


    Yes the critiriea will be in any worksheet on the same workbook. Its not just sheet2.


    And Sheet1 also not specific. it will be active worksheet.
    In that case,
    please ignore test2 macro.

    Try this one :

    Please Login or Register  to view this content.
    This code won't activate sheet for the user.
    So, the user need to select the sheet, then choose the criteria range.
    Once the user click OK in the input box, it automatically comes back to the previous active sheet (in this case Sheet1).

    So please remember that the user has to start in a sheet where the data to be filtered resides.
    Last edited by karmapala; 05-20-2020 at 07:53 AM.

  9. #9
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    Hi karmapala Code 3, after updating Header row on input box, its browsing to select file......

  10. #10
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    I am out now.
    Sorry...I just realized that there is still a hard coded variable in my code.

    I will check after i am back.

  11. #11
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    I hope this time the code works for you

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    Hi karmapala.

    Test4 working as expected. Thanks for your help.

  13. #13
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    I am trying to create the "Output" sheet from the code itself, hence added the below code. after selecting range its goes to output tab. may be i added in wrong place.

    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    Quote Originally Posted by SARAN89 View Post
    I am trying to create the "Output" sheet from the code itself, hence added the below code. after selecting range its goes to output tab. may be i added in wrong place.

    Please Login or Register  to view this content.
    It seems you are correct putting the new line before the "Set Dest".

    Please remember, the code is using a column helper right after the last column of the table range.
    So please make sure that there will never exist any important value in any row of the column after the last column of the table range.

    Quote Originally Posted by SARAN89 View Post
    Hi karmapala.

    Test4 working as expected. Thanks for your help.
    You're welcome. Glad I can help
    Last edited by karmapala; 05-20-2020 at 09:58 AM.

  15. #15
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    Hi karmapala

    If i Add below i am not getting nothing on output file.

    Please Login or Register  to view this content.
    After adding the Worksheet "OutPut" may be the code considered that worksheet as active worksheet. That may be the issue.

  16. #16
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    111

    Re: Filter Specific data and copy paste to new worksheet

    I am trying to make small changes on this code on the below line, like inputting text or number or Range of Cell
    Please Login or Register  to view this content.
    But its prompting only for Range of Cell. seeking experts help.....

  17. #17
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    Quote Originally Posted by SARAN89 View Post
    Hi karmapala

    If i Add below i am not getting nothing on output file.

    Please Login or Register  to view this content.
    After adding the Worksheet "OutPut" may be the code considered that worksheet as active worksheet. That may be the issue.
    Sorry, I forgot to tell you that once the new sheet is added,
    this new sheet is become the active sheet.

    So maybe try to add a line like this :
    Please Login or Register  to view this content.
    Please remember, if there is already the sheets with name "Output",
    if you accidentally run this code again, it will complain that the sheet with name "Output" already exist.

    So, the better way is do the checking first whether the sheet with name "Output" already exist or not.

    Please Login or Register  to view this content.
    But if you are sure that you will always run the macro one time only, then I think to use the first code is OK.

  18. #18
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Filter Specific data and copy paste to new worksheet

    Quote Originally Posted by ayyappan80 View Post
    I am trying to make small changes on this code on the below line, like inputting text or number or Range of Cell
    Please Login or Register  to view this content.
    But its prompting only for Range of Cell. seeking experts help.....
    I think if you use type:=8, then this for a range.
    If you want to put a text, then the code is without the "Set" and without the type.
    Please search on the internet about inputbox type.

    Or read this : link
    Last edited by karmapala; 05-21-2020 at 06:14 PM.

  19. #19
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    98

    Re: Filter Specific data and copy paste to new worksheet

    Hi Karmapala.

    Now its fine. thanks for your help.

+ 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. [SOLVED] VBA to copy specific columns A, D, G, & H and paste in Filter Data tab
    By Neilesh Kumar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-27-2020, 03:12 PM
  2. [SOLVED] Need help on copy data and paste to the specific columns in another worksheet
    By nelsonlauo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-19-2019, 06:25 AM
  3. Copy & Paste to a specific row in another worksheet and clearing data from the form
    By k.stevens in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-26-2019, 09:47 AM
  4. Copy Data from One Worksheet and Paste it into a specific Worksheet in a different workboo
    By kingsdime29x in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2018, 02:09 PM
  5. [SOLVED] VBA to filter data, copy and paste into new worksheet
    By ngan202 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-14-2016, 08:12 PM
  6. how to filter and copy data from one sheet and paste to new worksheet
    By mr_asrul in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-03-2008, 07:15 AM
  7. Replies: 5
    Last Post: 03-18-2006, 08:45 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1