How to Extract Data between Two Date Ranges in Excel VBA

The purpose of this blog post is to extract data between two date ranges using Excel VBA.

Copying data from one ranges to another is an easy task but copying data between any two date ranges, is little bit tricky.

With the help of VBA coding, we will extract data between any two date ranges.

Now suppose that we have a huge dataset, as you can see below image:-

Extract Data between Two Date Ranges Image_01

Here we have two sheets “Data” where we have our original dataset from 01 January to 31 December.

And “Reports” sheet, where we want to paste or extract our data set between two date ranges.

You can copy or extract the dataset in the same worksheet also but here we are using a different worksheet (“Reports”).

Extract Data between Two Date Ranges_Image_02

So these are our two worksheets “Data” and “Reports”.

In “Data” sheet we our dataset, from 01 January 2022 to 31 December 2022.

This VBA macro will work if your dataset is bigger than this.

Here we will apply “Advance Filter” feature in VBA to get the desired result.

Extract Data between Two Date Ranges

Now go to the “Reports” sheet and copy all the header part where we want to paste our dataset.

Because we are using “Advance Filter” in VBA here, so we need to copy all the header part.

See in the image above, we have copied all the header part our dataset.

Now we will set the date ranges, from start date to end date.

See in the image below:-

In this portion, we can enter our date ranges with start and end dates.

Advance Filter has below parameters to complete to get our desired result.

These 3 parameters are explained as follows:-

List Range: – this belongs to our complete dataset range.

Criteria Range: – this is the criteria or condition, which we want to get to extract the data

Copy to: – this belongs to the location, where we want to put our filter dataset.

Here we have set our two parameters.

List Range: – our entire dataset in “Data” tab

Copy to: – this is the place where we want to get our results, i.e. in “Reports” tab.

And need add one more parameters, which is “Criteria”.

We have set our start date and end date but need to link these cells with our criteria cells.

See the below image, how to set our criteria:-

Now we have set our criteria also.

Just understand that.

Start Date: – our start date must be greater than or equal to cell range “I2”.

End Date: – our end date must be less than or equal to cell range “I3”.

And we have linked the criteria ranges cells with date ranges with “&” (ampersand) sign, you can see in the image.

Our third parameters “Criteria” is also completed.

Now we will create a “Button” to execute the VBA macro.

Go to “Insert” tab in Excel and in the “Illustrations” group, go to the Shapes dropdown arrow and select the desired shape as per your need.

See in the above image.

We have took a shape and named it as “Extract Data” as we have mentioned in the above image.

Now the final part of this tutorial is our VBA macro.

Go to the Developer tab in Excel and then select the “Visual Basic” tab.

Once you click on it, the Visual Basic for Application” window will open.

Go to “Insert” tab here and create a new “Module” here.

And write your VBA macro, as I have mentioned below.

If you want to copy the VBA code, you can copy it from below section but make sure you make necessary changes.

This is a single line VBA code but I have written it in 3 lines.

You need to add (“_”) at the end of each line after putting one space, as I did here.

Sub Extract_Data_between_Two_Date_Range()
Worksheets(“Data”).Range(“A1”).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets(“Reports”).Range(“H5:I6”), _
CopytoRange:=Worksheets(“Reports”).Range(“A1:F1”), Unique:=False

End Sub

Image_09

See the above VBA code, it is completely fulfilling all the parameters of Advance Filter in Excel.

Now if you want to hide the date criteria portion, you can do it by just place the button above the date range portion.

Just right-click on the button and it will change into design mode.

Once the button converts into design mode, you can replace it into anywhere you want to relocate it.

Now one more essential task still remaining.

We have create the VBA macro and button also.

But how this button will work without connecting our VBA macro with it.

Now we will connect our macro to this button.

Simply right-click on the button, and select the option “Assign Macro” from all the listed option.

Select the macro from the list, which you want to connect with Button, if you have created many.

And press “Ok” button.

Once you assign your VBA macro with this button, it is ready to give you the result.

Just put the Start Date and End Date in the field provided and press button below, it will quickly extract the data for you.

Just change the start date and end date randomly with different date ranges, and see the result how this code will work.

So this is all about how you can extract dataset between two date ranges with the help of VBA macro.

If you want to learn more about this, please visit Here.

I hope you find this tutorial useful.

Please put your comments or suggestion in comment box provided below.

Extract Data between Two Date Ranges

Leave a Comment Cancel Reply