Consolidate Filtered Data From Multiple Sheets Excel VBA Macro

In this post we see how to consolidate filtered data from multiple sheets into separate sheets for each unique entry using Excel VBA macros. The macro loops through each worksheet and filters each unique entry in column A, then adds a new sheet for each unique entry to copy/paste the data. It is a modification of a previous macro (Copy Filtered Data To New Sheets), that allows to combine data from various sheets.

Macro/VBA code:

 

  Sub ConsolidateDataMultipleSheets()

      Dim ws As Worksheet, lr As Long, brand As Variant

      Dim uniqueBrands As New Collection, entry As Variant

 

      For Each ws In Worksheets

          With ws

    

           'get unique entries (brand name) in that sheet (ws)

           lr = .Cells(Rows.Count, "A").End(xlUp).Row

           On Error Resume Next

           For Each entry In .Range("A2:A" & lr)

               uniqueBrands.Add entry, entry

           Next

 

           'filter and copy/paste the data

           .Range("A1").AutoFilter

           For Each brand In uniqueBrands

               brand = brand.Value

               If Sheets(brand) Is Nothing Then Sheets.Add.Name = brand

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

               lr = Sheets(brand).Cells(Rows.Count, "A").End(xlUp).Row

               If lr = 1 Then

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

               Sheets(brand).Range("A1")

               Else

               .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy _

               Sheets(brand).Range("A" & lr + 1)

               End If

               .ShowAllData

           Next brand

 

           End With

       Next ws

  End Sub

 

 

Macro explained:

• We declare an object variable to store each worksheet (ws), a variable to store the row number of the last row with content (lr), and a Variant to store the elements (brand) in a collection of unique entries (unique brands). Then we declare uniqueBrands as a new collection and entry as Variant to get unique entries in the given range (see later).
• With each worksheet (in the example above that’s Dealer1, Dealer2, etc), we get first the unique list of entries in column A (for brands). For that, we loop through all values in column A (from A2 up to the last row with content – lr), and attempt to add each entry to the key with that same value to the collection. When the value is repeated that generates and error, and therefore we need to anticipate with error handling. In that way, it only adds the same value once, thus creating a collection of unique entries.
• Then we turn on filtering in that worksheet and start looping through the collection of unique entries (uniqueBrands). For each brand (Variant), we get the value of brand reusing the same variable. We could be using another variable too.
• We filter the data by brand (Criteria1) in column A (Field), and add a sheet with the brand name, unless there is already a worksheet with that name.
• Now we get the last row with content in the new sheet. If it’s empty (lr=1), we copy the source data including the header and paste it through A1. However, if there’s already some data in that sheet (lr>1), we use Offset to copy a row below and skip the headers, and then paste after the last row with content (lr +1).
• Finally, we unfiltered everything using the ShowAllData property of the activated worksheet (ws) and move to the next brand in uniqueBrands for that sheet.
• The process repeats as many times as initial sheets in the workbook.

 

This is how we consolidate filtered data from multiple sheets in Excel with VBA macros.

 

Other examples:

• How to Combine All Workbooks In Folder with Excel VBA macros
  
• How to Copy Columns To Separate Sheets with Excel VBA macros
  
• How to Stack Combine Multiple Columns with Excel VBA macros