Excel VBA Pivot Table to Filter Between Two Dates

Get FREE Advanced Excel Exercises with Solutions!

A Pivot Table is a great tool to quickly summarize a large amount of data in Excel. Suppose you have a huge data consisting of so many dates but all you want is just having the data in between two specific dates. Filtering a pivot table can easily help you out on this matter. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to filter between two dates in a pivot table in Excel with the VBA macro.


How to Create Pivot Table in Excel?

But before diving into filtering the pivot table, you need to know how to create one. There are some very good articles on our Exceldemy website on how to make a pivot table, how to create a pivot table using shortcuts, how to create a pivot chart in Excel and so many more. If you have already gone through those articles, then you have a solid idea of pivot tables. But for the arguments for this article, we will still provide you with a brief of how to create a Pivot Table in Excel.

The following image is the table from which we will create pivot tables.

Dataset of making excel vba pivot table filter between two dates

Steps to Create Pivot Table:

  • Firstly, select any cell from the table.
  • Secondly, click on the Insert tab.
  • Thirdly, select PivotTable -> From Table/Range.

Making excel vba pivot table filter between two dates

  • Following that, a pop-up box will appear. You will see the whole range of the table or if you named your table then the name of the table (in our case, the name of the table is Table1) will automatically appear in the Table/Range section of the box.

If you don’t know how to define a name to a table, then look at the following image and the steps provided.

    • First, select the whole table.
    • Then, click on the Table Design tab.
    • There, you will see a field named Table Name. Under that field, you can provide any name that you want for your table (in our case, we gave Table1 as the table name).

Now that you know how to define a name for a table, let’s get back to where we left off for our actual target which is creating a pivot table.

  • After selecting the table or a range, then it is time to provide a destination for the new pivot table. If you want to store the new pivot table in a new worksheet, then check the New Worksheet. Or if you want to store the pivot table in the same worksheet then check the option Existing Worksheet.

It is best practice to store the pivot table in a new worksheet, but we will store it in the same worksheet so that it is easier for you to understand the comparison of the data between the source table and the pivot table.

  • Then, in the Location field, simply select the cell from where you want to start storing the pivot table. You will see the sheet name and the cell reference number will automatically be input in the Location box. For our case, the sheet name is Dataset, and the destination cell is K4.
  • Later, click OK.

Selecting range for excel vba pivot table filter between two dates

  • Look at the image below. This is what it will look like after creating a pivot table in Cell K4.

Newly created excel vba pivot table filter between two dates

  • From here, you can create as many pivot tables as you want by dragging and dropping the fields from above to the areas below.

Just remember some things before going any further with your pivot tables.

    • Usually, the hierarchies or the grouping fields go into the Columns area.
    • The non-numeric fields fall into the Rows area.
    • And the numeric fields go into the Values area.
  • We created two pivot tables, one with only Dates and Sales and another with all the data that we have in the source table.

We did this for you so that when we filter, you can have a clear idea of what is actually happening with the final pivot table that’s been created by all the data by seeing the pivot table that’s been created only by dates and sales. As the final pivot table usually becomes crowded with lots of data so it will be easier for you to learn from the pivot table created only from the dates and sales value.

Created excel vba pivot table filter between two dates

  • Usually, pivot tables hold names like PivotTable1, PivotTable2 and so on. These types of names usually don’t have any meaning. We want to have an idea of what the table refers to by seeing the name. So, to grant meaningful names for the pivot tables,
    • First, click on the first cell of the table.
    • Then, click on the PivotTable Analyze tab.
    • There will be a field named PivotTable Name in the PivotTable group. Under that field, you can provide any name that you want for your table.
  • In our case, we supplied the name PvtByDate for the pivot table created only by the Dates and Sales values.

Naming the first table in excel vba pivot table filter between two dates

  • And, for the pivot table created with all the values, we gave it the name PvtByAll.

Naming the second table in excel vba pivot table filter between two dates

You already know which type of fields fall under which type of category from the above discussion. But still, here is the overview of the pivot tables created for you to understand. You can easily know which fields are fallen under which area from the two images below.

  • For the PvtByDate table,
    • All the dates and related fields of it – Years, Quarters, Months – go into the Rows area.
    • Numeric values, such as Sales, falls into the Values area.

Fields for the first table of excel vba pivot table filter between two dates

  • For the PvtByAll table,
    • All the dates and related fields of it – Years, Quarters, Months – go into the Rows. The non-numeric values, such as Product, Province, also fall into this area.
    • Numeric values, such as Sales, Qty., go into the Values area.

Fields of the second table for excel vba pivot table filter between two dates

Now, we have finished creating the pivot tables from the source table for our article.

The next part is, how to filter between two dates in these created pivot tables with VBA code in Excel.


Excel VBA Pivot Table to Filter Between Two Dates

In this section, we will show you how to filter between two dates in the pivot table only by clicking a button in Excel with VBA macro. It means, every time we click on the button, the data from a large spreadsheet will automatically be filtered according to two specific dates.

Embed VBA Macro to Filter Between Two Dates in Pivot Table with Button in Excel

To filter between two dates, we need two dates to be assigned to our dataset.

  • First, assign two cells in your dataset for a start and an end date. As shown in the image below, we will store the Start Date in Cell C4 and End Date in Cell C5.

But before storing the dates, we need to validate those two cells first. So that, when there are no valid dates in those cells, Excel will warn us with messages.

  • To validate the cells,
    • First, select the cells (cells C4 and C5).
    • Then, go to tab Data.
    • Next, select Data Validation from the Data Tools group.

Validating dates for excel vba pivot table filter between two dates

    • From the appeared Data Validation pop-up box, do the following:
      • Pick Date in the Allow criteria.
      • Select between in the Data criteria.
      • Provide a start date (for our case, it is 1/1/2016) and end date (the date is 12/31/2017 in our case) in the Start date and End date criteria field respectively.
    • After that, click OK.

Once you have validated the cells, now it’s time to store the date values in those cells as well.

  • Write the start date (e.g., 1/1/2016) value in Cell C4 and end date (e.g., 12/31/2017) value in Cell C5.

Storing dates for excel vba pivot table filter between two dates

And just by assigning the dates in our dataset, we are now at the very last stage of finalizing our dataset.

We want to filter the table between two dates by clicking a button, right? So now let’s create a button for our worksheet.

Steps to Create a Button to Filter Between Two Dates in Pivot Table:

  • To assign a button in our dataset, go to the Developer tab.
  • From there, click Insert and select Button under the Form Controls group.

Creating button for excel vba pivot table filter between two dates

  • There will be a plus symbol (+). Drag the symbol to create a button in any size anywhere in your spreadsheet.

  • Once you finished dragging the symbol, there will be a pop-up box named Assign Macro. Write the Macro name that you want the sub-procedure of your VBA code to be defined by. In our case, we name the macro as FilterByDates.
  • Then, click New, as it is a new VBA macro.
  • Later, click OK.

  • You will be taken into an auto-generated VBA code window.

  • Then, copy the following code and paste it into the code window.
Sub FilterByDate()
If Range("C4").Value = "" Then
MsgBox ("Please Enter a Valid Start Date")
Exit Sub
End If
If Range("C5").Value = "" Then
MsgBox ("Please Enter a Valid End Date")
Exit Sub
End If
With ActiveSheet.PivotTables("PvtByDate").PivotFields("Years")
.ClearAllFilters
.PivotFilters.Add Type:=xlDateBetween, Value1:=Range("C4").Value, Value2:=Range("C5").Value
End With
With ActiveSheet.PivotTables("PvtByAll").PivotFields("Years")
.ClearAllFilters
.PivotFilters.Add Type:=xlDateBetween, Value1:=Range("C4").Value, Value2:=Range("C5").Value
End With
End Sub

Your code is now ready to run.

VBA to pivot table filter between two dates in Excel

  • Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After successfully executing the code, you will get the pivot tables with the filtered date values.

Result of excel vba pivot table filter between two dates

Now, every time running a macro to filter data is kind of a mundane way to get any job done. That’s the reason we planned to create a button to filter in the first place. So that we can just simply click on the button every time we need to filter our worksheet.

We have written the code inside a button control, remember? Now let’s get back to it to modify the appearance of the button.

    • Double click on the text of the button. It will go into the Edit mode. Then give it any name that you want. We defined our button by Click To Filter.
    • Then, right-click on the button and select Format Control…

    • From the Format Control pop-up window, modify the button the way you like For example, we style the text with Bold font and make the size 14, also with colouring it.
    • After styling your button, click OK.

Look how appealing our button looks now in the image shown below!

We created the button to filter dates, right? So, button styling processes are optional, you can do it if you want, or you can omit it. The button will still work even if it looks unappealing. Or it won’t? Let’s find it out.

  • First, clear all the filters that happened when we run the macro. To clear filters, click on the dropdown list right next to the table headers.
  • From the appeared option list, select Clear Filter From “Years”. Clear filters from both tables in the same way.
  • Then, press OK.

  • Now, click the button. You will see the pivot tables are being filtered according to the condition, only the dates between 1/1/2016 and 12/31/2017 are being displayed there.

Overview of excel vba pivot table filter between two dates

We have successfully learned how to create pivot tables from a source table and how to filter between two dates in those pivot tables with VBA in Excel.

VBA Code Explanation

If Range("C4").Value = "" Then
MsgBox ("Please Enter a Valid Start Date")
Exit Sub
End If

This part of the code will make sure that if there is no valid date in Cell C4 then it will warn the user by showing a message.

If Range("C5").Value = "" Then
MsgBox ("Please Enter a Valid End Date")
Exit Sub
End If

This part of the code will make sure that if there is no valid date in Cell C5 then it will warn the user by showing a message.

With ActiveSheet.PivotTables("PvtByDate").PivotFields("Years")
.ClearAllFilters
.PivotFilters.Add Type:=xlDateBetween, Value1:=Range("C4").Value, Value2:=Range("C5").Value
End With

This piece of code:

  • first clears all the existing filters from the PvtByDate pivot table.
  • You will find the PivotFields name (“Years”) in the Rows area of the pivot table created.
  • After that, it will filter the dates by the Type provided here – xlDateBetween – meaning the filter will occur between dates. And the date values are provided in the Value1 and Value2 We passed the cell reference numbers, of the start date (Cell C4) and end date (Cell C5) in those variables respectively.

If you don’t want to filter the PvtByDate table, then you can omit this piece of code. Then the code will filter the PvtByAll table only.

With ActiveSheet.PivotTables("PvtByAll").PivotFields("Years")
.ClearAllFilters
.PivotFilters.Add Type:=xlDateBetween, Value1:=Range("C4").Value, Value2:=Range("C5").Value
End With

This piece of code:

  • first clears all the existing filters from the PvtByAll pivot table.
  • You will find the PivotFields name (“Years”) in the Rows area of the pivot table created.
  • After that, it will filter the dates by the Type provided here – xlDateBetween – meaning the filter will occur between dates. And the date values are provided in Value1 and Value2 We passed the cell reference numbers, of the start date (Cell C4) and end date (Cell C5) in those variables respectively.

If you don’t want to filter the PvtByAll table, then you can omit this piece of code. Then the code will filter the PvtByDate table only.


Download Workbook

You can download the free practice Excel workbook from here.


Conclusion

To conclude, this article showed you how to filter between two dates in a pivot table simply by clicking a button in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


Related Articles


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo