Copy data having date between two defined dates to new worksheet using VBA

 

 

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.

 

ArrowRawData

 

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.

 

ArrowMainSheet

 

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.

 

ArrowAfterRunningMacro

 

ArrowOutputSheet

 

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



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>