In this article, we will create a macro to extract data from raw data worksheet to a new worksheet, based on the specified date range.
Raw data consists of three columns. First column contains dates, second column contains agent names and third column contains numbers of sales made by an agent on that particular date.
Before running the macro, two inputs are required from a user. User has to define the start and end date. Based on the specified dates, macro will extract the data between the defined date range to a new worksheet.
After specifying the start and end dates, user has to click the “Submit” button to execute the macro.
On running the macro it will sort data in the “RawData” sheet, based on date column and extract data based on the specified date range to the newly inserted worksheet.
Logic explanation
Macro takes input for start and end dates from cells J8 and J9, respectively. This macro firstly sorts the data in “RawData” sheet, based on column A in ascending order. We have sorted the data according to date values, so that we can copy the data in a single range, after applying filter for the defined range.
After sorting the data, apply the filter over it. Filter applied is based on two conditions, first condition is that value in column A should be greater than or equal to start date and second condition is that value in column A should be less than or equal to end date.
After applying the filter, the new worksheet is inserted and filtered data is copied and pasted into it.
Code explanation
Range("A1").CurrentRegion.Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
Above code is used to sort data in the defined range. Key1 specifies the column based on which data will be sorted. Sorting order is provided by order1. We have already defined the ascending order. To define descending order, one can use xlDescending constant. Header is used to specify whether data range contains the header.
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:= ">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
Above code is used to apply filter over the data range. Field1 specifies the column number on which filter will be applied. Criteria1 and Criteria2 define the conditions based on which data will filter. Operator specifies the operator, which will be used between two conditions.
Worksheets.Add after:=Worksheets(Worksheets.Count)
Above code is used to insert the new worksheet after the last worksheet in workbook.
One can easily understand the codes, as I have put comments along with the codes in the macro.
Please follow below for the code
Sub CopyDataBasedOnDate()
'Disabling screen updates
Application.ScreenUpdating = False
'Declaring two variables of Date data type
Dim StartDate, EndDate As Date
'Declaring variable for worksheet object
Dim MainWorksheet As Worksheet
'Initializing the Date variables with starting date from cell J8
'and end date from cell J9 of "Macro" sheet
StartDate = Sheets("Macro").Range("J8").Value
EndDate = Sheets("Macro").Range("J9").Value
'Initializing worksheet object with "RawData" worksheet
Set MainWorksheet = Worksheets("RawData")
'Activating the worksheet object
MainWorksheet.Activate
'Sorting the data by date in column A in ascending order
Range("A1").CurrentRegion.Sort _
key1:=Range("A1"), order1:=xlAscending, _
Header:=xlYes
'Filter the data based on date range between starting date and end date
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
'Copy the filter data
ActiveSheet.AutoFilter.Range.Copy
'Inserting new worksheet after the last worksheet in the workbook
Worksheets.Add after:=Worksheets(Worksheets.Count)
'Pasting the copied data
ActiveSheet.Paste
'Auto adjusting the size of selected columns
Selection.Columns.AutoFit
Range("A1").Select
'Activating the "RawData" sheet
MainWorksheet.Activate
'Removing filter from the worksheet which we applied earlier
Selection.AutoFilter
Sheets("Macro").Activate
End Sub
If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I have used this and when run the report it creates the sheet but filters only between the days and does not take into account the months/years. It will look at ie 14/05/2020 and 15/04/2020 will both appear if I search between 14/05/2020 and 16/05/2020 Can anyone help please
The wording is Kind of confusing; I'll see if this make sense; Two sheets, one named Macro, the other named RawData. On the sheet name Macro ; in cell J8 , put your start date, J9 put your end date. On the page labeled RawData, starting in cell A1 , put your dates, you can put 5/1/20 (format column as date 3/14/12 (it's in the format date dialog) in A1 and just drag it a couple months. run macro and all dates that fall between j8&j9 will be copied to new sheet. VBA works as advertised.
Hello
I tried the application and when I click on the button to generate the report it creates the sheet1 with the headers but no date is displayed. Can you help me understand the problem or just send me the file please. Thank you
Hi,
Check the dates in your file if they are correct and the data contains that date range. Check the date format as excel does not recognize some date formats. Check if the date format is in DD/MM/YYYY. most of the time the date format is MM/DD/YYYY.
I am not able to get the filtered data in excel file. would you please help. My excel is supporting data in format 02-02-2020 or 02/02/2020. They dont support MM/DD/YYYY format.
Let me see your code swagatika...
yes it seems to not recognise dates past 13 and gets confused even if the DD/MM format is changed to MM/DD so for instance 13/12/2019 it will not recognise please can you help?
ONLY HEADERS R COPIED,NO DATA ,PLS HELP
Dear Sir/Madam,
Many thanks for sharing our knowledge with the rest of us. I tried your suggestions above but no data was displayed. Would greatly appreciate if you could share the file with me.
Many thanks in advance.
Regards, Michael
Hello
I tried the application and when I click on the button to generate the report it creates the sheet1 with the headers but no date is displayed. Can you help me understand the problem or just send me the file please. Thank you