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:
Option Explicit

Sub copyData()
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)
MsgBox mymonthnum

Dim mymonthname As String

mymonthname = MonthName(mymonthnum)

MsgBox mymonthname

ThisWorkbook.Worksheets(mymonthname).Cells.ClearContents

Dim rngOutput As Range
Set rngOutput = ThisWorkbook.Worksheets(mymonthname).Range(“A1”)

rngData.AdvancedFilter xlFilterCopy, rngCriteria, copytorange:=rngOutput, Unique:=False

ThisWorkbook.Worksheets(mymonthname).Columns.AutoFit

End Sub