Conditional formatting

bobby440 

Here is an example with VBA like i send it in other message.

Just hoping this is the desired result. If not, please provide more detailed and step-by-step (cell by cell, sheet by sheet) instructions for the project.

Option Explicit

Sub ConsolidateData1()
    Dim wsMaster As Worksheet
    Dim ws As Worksheet
    Dim lastRowMaster As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim companyColMaster As Long
    Dim companyCol As Long
    Dim companyRowMaster As Long
    Dim companyRow As Long
    Dim company As String
    
    ' Set reference to the master worksheet
    Set wsMaster = ThisWorkbook.Worksheets("Master")
    
    ' Find the last row and column in the master worksheet
    lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row
    lastCol = wsMaster.Cells(1, wsMaster.Columns.Count).End(xlToLeft).Column
    
    ' Find the column number for the "company" column in the master worksheet
    companyColMaster = wsMaster.Rows(1).Find(What:="company", LookIn:=xlValues, LookAt:=xlWhole).Column
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Skip the master worksheet
        If ws.Name <> "Master" Then
            ' Find the last row in the current worksheet
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            
            ' Find the column number for the "company" column in the current worksheet
            companyCol = ws.Rows(1).Find(What:="company", LookIn:=xlValues, LookAt:=xlWhole).Column
            
            ' Loop through all rows in the current worksheet (skipping the header row)
            For companyRow = 2 To lastRow
                ' Get the name of the company in the current row
                company = ws.Cells(companyRow, companyCol).Value
                
                ' Find the row number for this company in the master worksheet
                companyRowMaster = wsMaster.Columns(companyColMaster).Find(What:=company, LookIn:=xlValues, LookAt:=xlWhole).Row
                
                ' Copy data from the current row to the corresponding row in the master worksheet
                ws.Range(ws.Cells(companyRow, 2), ws.Cells(companyRow, lastCol)).Copy _
                    Destination:=wsMaster.Cells(companyRowMaster, 2)
            Next companyRow
        End If
    Next ws
End Sub

NikolinoDE

I know I don't know anything (Socrates)

Was the answer useful? Mark them as helpful!

This will help all forum participants.