How to Copy Data to a New Workbook Based on Dates
How do I copy data between two dates from one workbook to a new workbook?
Suppose you have a “master”-type workbook that contains ALL the data for all the dates, spread out amongst many sheets, like this:
You don’t necessarily need all that data for your analysis — in fact, you are only interested in certain small date range subsets, like, for example, everything that happened between January 5 and January 10:
How can you get that specific date range of values from each worksheet in a new workbook? You certainly don’t want to filter each sheet by hand… that would take forever! Let’s crack this nut with VBA:
Woo! Here’s the code that gets it done:
Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.
Let’s walk through this 2-part code using our 4-step VBA process to guide the way:
Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup
The first subroutine,
PromptUserForInptDates, is short but sweet. There is no Step 1 – Setup, Step 2 – Exploration or even Step 4 – Cleanup necessary! It’s all execution here, and beyond that it’s essentially a repeat.
Lines 9-17 prompt the user to input the start date as a
String then validate what was entered. For example, if the user puts a non-date into the box, our subroutine will catch the problem and exit instead of creating an error:
Nice! Unhandled errors are scary to users, and this is a place where we can easily guess what mistakes might happen, so it’s a good idea to go ahead and handle them.
Lines 20-28 do the same thing as lines 9-17 — this time, we get the end date as a
String (instead of the start date).
Finally, line 31 calls the
CreateSubsetWorkbook subroutine, using the validated-strings we just obtained — now it’s time for the fun stuff!
In a lot of ways, the whole subroutine above
PromptUserForInputDates handled MOST of our Step 1 – Setup, but lines 44-45 take care of the final bit. We know the dates are in column C, so we assign
lngDateCol to 3. Then, we create a new
Workbook and assign it to
Our Step 2 – Exploration begins inside the loop that we kick off on line 48, which iterates through all the
First, on lines 52-53, we create a new
Worksheet and assign its name to be the same name as the corresponding sheet in
ThisWorkbook. Next, on line 57, we create a “target”
Range, which is where we’ll paste our filtered data. Then, lines 62-66 find the last-occupied row and column on the sheet, and line 67 assigns the entire data block to our
rngFull variable. Woo — now we’re ready for the amazing, built-in
Lines 71 through 80 are where the magic happens. We apply the
rngFull, with the following configuration:
Fieldis the column we’re interested in, which we know is 3 (i.e.
Criteria1, which is the first condition we want applied to
rngFull, we set to a concatenation of
Criteria2, which is the second condition we want applied to
rngFull, we set to a concatenation of
This gives us a nicely-filtered set of visible cells, which we assign to a “result” variable (
rngResult) on line 78. Finally, we copy
rngTarget, which we set in the beginning of our loop. Boom!
Since we do not want to leave the filters on, lines 83-86 clear all
AutoFilter states safely.
Step 4 – Cleanup happens on line 91, which we let the user know that our data has been transferred to the new file.
And with that, we’re done!
Are you masterfully creating new Workbooks with filtered data? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
Get the VBA Toolbelt!
Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.