Uploaded October 2019 | Updated September 2026, 2 weeks ago

---

How to copy data from one worksheet to another automatically and quickly using advanced filter with VBA.
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