+ Reply to Thread
Results 1 to 12 of 12

Filter Date Rows and copy entire data to new worksheet

  1. #1
    Forum Contributor
    Join Date
    05-25-2020
    Location
    Madurai
    MS-Off Ver
    Excel 2010
    Posts
    136

    Post Filter Date Rows and copy entire data to new worksheet

    Hi My Question is similar to the below thread

    Thanks to Dangelor and Nakw83 for the original code provided in the below thread

    https://www.excelforum.com/excel-pro...ster-file.html

    From Selected Excel files, From Row 26 Column D is having date. (Multiple Excel Files)

    Need to filter those rows and select entire data to new work sheet. (one by one from selected excel files)

    The above code is working for CSV file but not working excel. The excel file is out put of some report, its having some columns are merged.

    Seeking help to modify the above code.

    Enclosed sample workbook of one.


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by KKR1975; 08-04-2020 at 04:00 AM.

  2. #2
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    MEXICO
    MS-Off Ver
    Excel 2013
    Posts
    212

    Re: Filter Date Rows and copy entire data to new worksheet

    Your test.xls file is empty.
    The code that you put generates a formula, do you really want that?
    Better explain how your data is on the sheet of xls books, and what data you want to obtain.
    It would be better if you share a book with data, show in that book which ones you want to copy.

  3. #3
    Forum Contributor
    Join Date
    05-25-2020
    Location
    Madurai
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: Filter Date Rows and copy entire data to new worksheet

    Hi Dante Amor

    Thanks.

    May be some issue in my sample workbook. I have re attached the same for your reference

    Requirement is as below

    Refer Tab Sample Data

    First I need to select excel files ( Multiple)
    Each excel file is having same kind of data like below

    Column D is having "dates" starting from Row 26
    I want to filter Column D and select only Dates, then I want to paste it in another sheet.
    one by one of files selected.
    Attached Files Attached Files

  4. #4
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Filter Date Rows and copy entire data to new worksheet

    Hi KKR1975,

    Try to unmerge the cells ... Add below red line
    Please Login or Register  to view this content.
    Last edited by nankw83; 08-07-2020 at 03:27 AM.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  5. #5
    Forum Contributor
    Join Date
    05-25-2020
    Location
    Madurai
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: Filter Date Rows and copy entire data to new worksheet

    Hi Nankw83

    Thanks for your help

    I tried with above suggested lines, i am getting error on the below line(Error -autofilter method of range class failed 1004)

    Please Login or Register  to view this content.




    Please Login or Register  to view this content.

  6. #6
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Filter Date Rows and copy entire data to new worksheet

    Made couple of changes ... Try now

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    05-25-2020
    Location
    Madurai
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: Filter Date Rows and copy entire data to new worksheet

    Thank you so much for your help

    Its perfectly working as expected. once again thanks

  8. #8
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Filter Date Rows and copy entire data to new worksheet

    Glad to help & thanks for the reps

  9. #9
    Forum Contributor
    Join Date
    05-25-2020
    Location
    Madurai
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: Filter Date Rows and copy entire data to new worksheet

    On my orginal file column "D" rows i am having other then dates. like some numbers rows are there its also considered while pasting to another sheet. can you help how to remove exclude that
    Attached Images Attached Images

  10. #10
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Filter Date Rows and copy entire data to new worksheet

    How about this

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    MEXICO
    MS-Off Ver
    Excel 2013
    Posts
    212

    Re: Filter Date Rows and copy entire data to new worksheet

    Just comment that this line should also have the reference of the sheet:

    Set Rg = Ws.Range(Ws.Cells(1, 1), Ws.Cells(lrow, lCol))

  12. #12
    Forum Contributor
    Join Date
    05-25-2020
    Location
    Madurai
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: Filter Date Rows and copy entire data to new worksheet

    Hi Nankw83

    Thanks revised code working fine.

+ 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] Filter only date and copy to new with entire columns
    By SARAN89 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-08-2020, 07:32 AM
  2. Move entire rows from one worksheet to another based on a date
    By samnessy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-29-2019, 01:25 PM
  3. Copy Entire Row On Filter Data Including Hidden Columns
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2014, 04:17 AM
  4. Search for specific date range, copy entire row and paste to new worksheet
    By turias in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2013, 03:57 PM
  5. Replies: 1
    Last Post: 11-16-2012, 05:05 PM
  6. Macro to copy entire group of rows to another worksheet and another workbook
    By exc1967 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-27-2011, 04:52 PM
  7. Replies: 2
    Last Post: 05-19-2005, 04:08 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