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.
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.
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.
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
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 firstname.lastname@example.org