Excel VBA Macro: - Apply Filter and Copy, Paste Data Into Another Worksheet

This tutorial will explain how to Apply Filter and Copy, Paste Data Into Another Worksheet in Excel VBA.

Suppose, we have a huge employee dataset. And where column, “A” has employee names. You can see that there are some names repeated but the rest of the data is different.

First, we will apply the filter for any names and see how many records this employee has.

So here we will select the header and apply the filter on it.

Then with the help of VBA macro, we will create a new sheet of the name we have applied filter and copy the filtered data and paste it into the new sheet.

The same procedure we will follow for all the unique employee names and create new sheets for them. And apply a filter with their names and paste it into the respective sheets.

See in the below image, the dataset which we are using in this tutorial.

Apply Filter and Copy, Paste Data Into Another Worksheet_Image_01

Here in this dataset, we can see that there are multiple records in column “A” as repeated.

Now we will apply the filter for any of the names from the list and see how much data this employee has.

Here we have applied a filter for “Lee, Frank” one employee from the list.

See the image below:-

You can see that all the datasets for employee “Lee, Frank”.

Now with the help of VBA macro, we will create a new sheet for “Lee, Frank”.

And we will create a new sheet for this name (Lee Frank), and copy and paste the filtered data into it.

The same procedure will go on, for each unique employee create a new sheet and copy and paste the respective data into it.

We will all do this, with the help of the VBA code.

I have created a VBA code for this, you can see the full VBA code, and then we will split this code step-by-step.

See the complete code below.

Sub AutoFilter_With_Create_NewSheets()

Application.ScreenUpdating = False

Dim x As Range
Dim rng As Range
Dim Last_R As Long
Dim sht As String

‘specify sheet name in which the data is stored
sht = “Data Set”

‘change filter column in the following code
Last_R = Sheets(sht).Cells(Rows.Count, “A”).End(xlUp).Row
Set rng = Sheets(sht).Range(“A1:H” & Last_R)

Sheets(sht).Range(“A1:A” & Last_R).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(“AA1”), Unique:=True

For Each x In Range([AA2], Cells(Rows.Count, “AA”).End(xlUp))
With rng
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:=x.Value
    .SpecialCells(xlCellTypeVisible).Copy

Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
ActiveSheet.Paste
Range(“A1”).CurrentRegion.Columns.AutoFit
End With
Next x

‘ Turn off the filter
Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub

VBA Code – Apply Filter and Copy, Paste Data Into Another Worksheet

Now we will break this code into multiple shortcodes.

Apply Filter and Copy, Paste Data Into Another Worksheet

First of all, go to the Developer tab and click on the Visual Basic tab, you will get the below image

First, start the macro with the macro name and declare some variables, as you can see in the below image:-

Now set the variables for their path or location.

sht = sheet name where our data is stored, in case, if you have multiple sheets, then specify sheet name.

Last_R= Last row of column A for the dataset, we can find the last cell with the data

rng = This variable will set our entire dataset till the last cell value of our existing dataset.

So first, we must know how many unique employees are there, so we can create the same number of sheets. And copy and paste their respective data into the related sheets.

By applying the filter, we cannot get the list of all unique employees.

To find a unique name within a dataset we need to take the help of Advance Filter.

Advance Filter will find all the unique employee names in this dataset and at the same time copy the whole unique list at a different location.

To get the unique list in a different place, we have applied Advance Filter in VBA, see the image below:-

We have applied Advance Filter in employee column “A” and it will copy it at the same time in the location range (“AA1”).

In this case, column “AA1” is the location.

Once this code will run, your unique values will be copied into column “AA1”, see in the image below:-

This VBA code line is very important, with the help of this list, VBA will create a new sheet for each of its cell values appearing in from Range “AA2” to till the end of the list.

You can see here that cell AA1 has a heading, so cannot create new sheets for the heading.

Now we will use ‘For Each Loop here, and it will pick a name from this list and create a new sheet for that name.

We have declared a variable ‘X’ above in the macro, and this ‘X’ will be the value of cell “AA2” in the unique list name.

When we will apply the filter in the dataset, variable ‘X’ will pick the name from the list in column “AA”. And VBA will apply the filter for that name.

So every time it will apply a filter for a different name from the list and create a new sheet for the same.

So when we will run the VBA code, variable ‘X’ will change its value from cell “AA2” then “AA3” and so on.

See the code below:-

Applying for each loop in range “AA2” to till the last cell value

We will apply a filter within our entire dataset (rng).

Autofilter:= this will apply a filter in our entire dataset.

Field 1:- field 1 is our column no. 1 where all names are listed.

Criteria1– criteria are what to filter here, instruction is to pick a name from variable “x”, which is the first name in the list “AA” column.

When we will run this code, how it looks like, see the below image:-

See that the first name in our unique dataset is “Lee Fank”, when we will execute the code, it will find all data of “Lee Frank”.

See in the image below:-

By executing the next line, it will copy the filtered data as we have instructed in the VBA code.

Once the dataset is copied, then where to paste this data?

It will create a new sheet and paste the copied data into it.

So creating a new sheet, we have written VBA code as you can see below.

See how to create a new sheet and paste the copied data into that.

Execute this code to create a new sheet, see the below image:-

A new sheet is created for the ‘x’ value from the list “AA” column.

The next code is to paste the copied data into a new sheet.

The copied data is pasted and the column width is outfitted by the next line of VBA code.

So this is done for one cell value or first unique record in our dataset.

Now this VBA code will create new sheets for all unique employees’ names listed in column AA and paste their respective data into it.

Once we will execute this VBA code by pressing the F5 or RUN button from the toolbar, you will get the below result.

See below this VBA code created a sheet for every unique value in column AA.

You can see that this VBA code has created all the new sheets for listed names in column AA.

If you click any of the sheets it will show all related or filtered records within it.

And one more sheet you can see

So this way we can apply filter within a dataset and create new sheets for unique records and paste the related data into it.

If you want to learn more about this, please visit Microsoft Office Support.

I hope you find this tutorial useful.

Please feel free to put your comments or suggestion in the comment box below.

Excel VBA Macro: – Apply Filter and Copy, Paste Data Into Another Worksheet

Apply Filter and Copy, Paste Data Into Another Worksheet

1 thought on “Excel VBA Macro: – Apply Filter and Copy, Paste Data Into Another Worksheet”

Leave a Comment Cancel Reply