How to copy data from one worksheet to another automatically and quickly using advanced filter with VBA.
A user has data in a worksheet called data with the headers date, item and quantity. He also has 12 more worksheets with the months’ names: January, February, March, April, May, June, July, August, September, October, November and December. He enters data into the data worksheet as and when the data is available. Later he wishes to move data of a relevant month to the matching month worksheet. For example, he would like to transfer all entries with July dates to the July worksheet. Using Advanced Filter and Date functions we can automate the complete process quickly and easily using Visual Basic for Applications (VBA). The data is transferred to the appropriate worksheet in seconds.
Here’s the complete VBA code:
Dim rngData As Range, rngCriteria As Range
Set rngData = ThisWorkbook.Worksheets(“data”).Range(“A1”).CurrentRegion
Set rngCriteria = ThisWorkbook.Worksheets(“data”).Range(“G1”).CurrentRegion
Dim mymonthnum As Integer
mymonthnum = Mid(Range(“G2”), 3, 2)
Dim mymonthname As String
mymonthname = MonthName(mymonthnum)
Dim rngOutput As Range
Set rngOutput = ThisWorkbook.Worksheets(mymonthname).Range(“A1”)
rngData.AdvancedFilter xlFilterCopy, rngCriteria, copytorange:=rngOutput, Unique:=False