Extract and copy data between two dates and paste them in another workbook

Interesting challenge -- filtering a "master" workbook to get subsets of data is one place where VBA really shines!

I wrote a step-by-step guide to the solution below, with screenshots and animations, here:

How to Copy Data to a New Workbook Based on Dates

Assuming you paste the following heavily-commented code into your "master" workbook, which is the one containing ALL the data, you should be all set!

Please Login or Register  to view this content.

Hey @forquaidian -- very interesting. Is no data at all copied from the master workbook to the new workbook, or is it perhaps just the header row?

I suggest putting break points on this section:

Please Login or Register  to view this content.

and stepping through it line by line, like this:

2-minute Video of stepping through break points on Filter / Copy Steps

This should show you if you are perhaps selecting a date range that is not contained in the master workbook.

Hey @SSndstorm -- I'm not sure I understand your comment. There are no VLookup functions being applied here, but rather Excel AutoFilter function. This function application is cleaned up for each sheet by the following lines:

Please Login or Register  to view this content.

Hi John,

thank you for your mail.

it is giving error "Type mismatch" at this line "If CDate(.Sheets(i).Cells(x, "C")) > start And CDate(.Sheets(i).Cells(x, "C")) < finish Then"

It will work i believe, please note that my data starts at row # 5 and column # 2 and date column is # 3.

please advise. i will be grateful to you.

regards
quaidian

please note that my data starts at row # 5 and column # 2 and date column is # 3.

That is a very important consideration -- the position of the data on each Worksheet is critical for the

Please Login or Register  to view this content.

method to work correctly.

The code below worked on my file after position all my data blocks to start in row 5, column 2. Here is a short video of the code in action:

Copying data that starts in row 5, column 2 to a new workbook

And here is the modified code:

Please Login or Register  to view this content.

Hi John,

it is copying only one row but not copying all rows between dates.

i am anxiously waiting for your reply.

regards
Quaidian

Hey @forquaidian -- OK, I now understand more about your problem. You (1) already have the two Workbooks and (2) already have matching Worksheets between them.

Do the Worksheets in the Destination Workbook (which is where we will paste our data to) already have data present? If so, does this data need to be cleared?

At this point it would be very beneficial if you could provide samples, with the present state of the Workbooks and the desired "end" state of the Workbooks.

The modified code below will:

1. Prompt you to enter a date (this is the date you would like to keep)
2. Validate the input date
3. Loop through each Worksheet, clearing all rows that do NOT match this date

This has a few important changes from the original design. First, it no longer copies data to a new Workbook. Second, it clears rows that do not match the input date (instead of deleting them). Finally, it prompts the user for a single input date for matching purposes, rather than two.

Please Login or Register  to view this content.