Copy Data Using Advanced Filter with VBA

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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.