Merge Multiple column from different sheet and highlight the row with unique value.

[anu_gv]

Hi,

I have multiple sheet in given workbook. Each sheet filled with Column A:E with same number of rows. I would like to combine all these sheets into one sheet and highlight the row for the column header named as "Order" with "Yes" option.

Sheet1

S.No	Order	Name	Full Name	Address
1	Yes	Alex	Alex BBBBB	12, xxxxxx St
2	No	Ben	Ben CCCCC	25, yyyyy st
3	No	Brian	Brian DDDDD	44, zzzzz Dr,
4	Yes	Charlie	Charlie EEEE	45, jjjjjjj Blvd
5	Yes	Dan	Dan GGGGGG	89, abc Dr,

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

Sheet2

S.No	Order	Name	Full Name	Address
1	No	Alex	Alex BBBBB	12, xxxxxx St
2	Yes	Ben	Ben CCCCC	25, yyyyy st
3	No	Brian	Brian DDDDD	44, zzzzz Dr,
4	Yes	Charlie	Charlie EEEE	45, jjjjjjj Blvd
5	No	Dan	Dan GGGGGG	89, abc Dr,

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

Sheet3

S.No	Order	Name	Full Name	Address
1	Yes	Alex	Alex BBBBB	12, xxxxxx St
2	Yes	Ben	Ben CCCCC	25, yyyyy st
3	Yes	Brian	Brian DDDDD	44, zzzzz Dr,
4	No	Charlie	Charlie EEEE	45, jjjjjjj Blvd
5	No	Dan	Dan GGGGGG	89, abc Dr,

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

Any help is really appreciated.

Thx
Anu.

 

[anu_gv]

To All,

Any suggestion (or) previously any one encountered the same request. Please help me out.

Thx
Anu.

 

[Fluff]

Try

Sub CopyToNewSheet()

   Dim Ws As Worksheet
   Dim NewWs As Worksheet
   
   Sheets.Add(Sheets(1)).Name = "New"
   Set NewWs = ActiveSheet
   NewWs.Range("A1:E1").Value = Sheets(2).Range("A1:E1").Value
   For Each Ws In Worksheets
      If Ws.Name <> "New" Then
         Ws.UsedRange.Offset(1).Copy NewWs.Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
   
   With NewWs.Range("B:B")
      .Replace "Yes", True, xlWhole, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).Interior.Color = 123456
      .Replace True, "Yes", xlWhole, , False, , False, False
   End With
End Sub

This will create a new sheet called "New" & copy all the other sheets on to it.

 

[anu_gv]

Thanks Fluff,
Instead of putting all the other sheet value in down in "New" sheet, is there a way to have it in columns such as "A,B,C,D,E", "J,K,L,M,N", "P,Q,R,S,T" from sheets - Sheet1, Sheet2, Sheet3 respectively in the "New" Sheet.

Thx
Anu.

 

[Fluff]

How about

Sub CopyToNewSheet()

   Dim Ws As Worksheet
   Dim NewWs As Worksheet
   Dim Col As Long
   
   Sheets.Add(Sheets(1)).Name = "New"
   Set NewWs = ActiveSheet
   Col = 1
   
   For Each Ws In Worksheets
      If Ws.Name <> "New" Then
         Ws.UsedRange.Copy NewWs.Cells(1, Col)
         Col = Col + 9
      End If
   Next Ws
   
   With NewWs
      For Col = 2 To .Cells(1, Columns.Count).End(xlToLeft).Column Step 9
         With .Columns(Col)
            .Replace "Yes", True, xlWhole, , False, , False, False
            On Error Resume Next
            .SpecialCells(xlConstants, xlLogical).Interior.Color = 123456
            On Error GoTo 0
            .Replace True, "Yes", xlWhole, , False, , False, False
         End With
      Next Col
   End With
End Sub

 

[anu_gv]

Thanks Fluff. Looks Good.
Regards
Anu.

 

[Fluff]

Glad to help & thanks for the feedback