Copy Filtered Data To New Sheets Excel VBA Macro

Excel offers filter options to easily narrow down the data. This macro example activates the filter option in VBA and copies the filtered data for each unique entry to separate sheets.

Macro/VBA code:

  Sub CopyFilteredDataToNewSheets()

      Dim r As Integer, brandName As String

      With Worksheets("All")

          .Range("A1:C1").AutoFilter

          For r = 2 To 24

              brandName = Sheets("All").Range("A" & r).Value

              On Error Resume Next

              If Sheets(brandName) Is Nothing Then

                  .Range("A1:C1").AutoFilter Field:=1, Criteria1:=brandName

                  .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy

                  Sheets.Add.Name = brandName

                  Sheets(brandName).Paste

                  .ShowAllData

              End If

          Next r

      End With

  End Sub

Macro explained:

• We declare the row counter variable (r) as an integer, and the brandName variable as a string.
• Then we start a With statement to select the worksheet “All” in order to make sure filtering of data occurs on that sheet.
• Next we set the filter on in columns A through C with the AutoFilter property.
• We create a For loop to inspect the data row by row and get the car brand name from column A. We start in row 2 and finish in row 24 as we know that’s the extent of our data set.
• Here we have an IF statement to check if a sheet with that brand name already exists. If not, we filter the data for that brand name (Field 1 corresponds to column A), copy the visible cells of the current region with data, and paste it to a newly added sheet. We use On Error Resume Next to avoid the run-time error of checking for a sheet that does not exist.
• We unfiltered everything using the ShowAllData property of the activated worksheet (All) and then end the With statement after looping though all rows.

This is how we copy filtered data to new sheets in Excel with VBA macros.

Other examples:

• How to Remove Unused Rows And Columns with Excel VBA macros 
• How to Open Workbook Safely with Excel VBA macros
• How to Add Button To Fit Selection with Excel VBA macros