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

[TABLE="class: grid, width: 365, align: left"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]S.No[/TD]
[TD]Order[/TD]
[TD]Name[/TD]
[TD]Full Name[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]Alex[/TD]
[TD]Alex BBBBB[/TD]
[TD]12, xxxxxx St[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]No[/TD]
[TD]Ben[/TD]
[TD]Ben CCCCC[/TD]
[TD]25, yyyyy st[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]No[/TD]
[TD]Brian[/TD]
[TD]Brian DDDDD[/TD]
[TD]44, zzzzz Dr,[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yes[/TD]
[TD]Charlie[/TD]
[TD]Charlie EEEE[/TD]
[TD]45, jjjjjjj Blvd[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]Dan[/TD]
[TD]Dan GGGGGG[/TD]
[TD]89, abc Dr,[/TD]
[/TR]
</tbody>[/TABLE]
















Sheet2

[TABLE="class: grid, width: 366, align: left"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]S.No[/TD]
[TD]Order[/TD]
[TD]Name[/TD]
[TD]Full Name[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]No[/TD]
[TD]Alex[/TD]
[TD]Alex BBBBB[/TD]
[TD]12, xxxxxx St[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]Ben[/TD]
[TD]Ben CCCCC[/TD]
[TD]25, yyyyy st[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]No[/TD]
[TD]Brian[/TD]
[TD]Brian DDDDD[/TD]
[TD]44, zzzzz Dr,[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yes[/TD]
[TD]Charlie[/TD]
[TD]Charlie EEEE[/TD]
[TD]45, jjjjjjj Blvd[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]No[/TD]
[TD]Dan[/TD]
[TD]Dan GGGGGG[/TD]
[TD]89, abc Dr,[/TD]
[/TR]
</tbody>[/TABLE]
















Sheet3

[TABLE="class: grid, width: 366"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]S.No[/TD]
[TD]Order[/TD]
[TD]Name[/TD]
[TD]Full Name[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]Alex[/TD]
[TD]Alex BBBBB[/TD]
[TD]12, xxxxxx St[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]Ben[/TD]
[TD]Ben CCCCC[/TD]
[TD]25, yyyyy st[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Yes[/TD]
[TD]Brian[/TD]
[TD]Brian DDDDD[/TD]
[TD]44, zzzzz Dr,[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]No[/TD]
[TD]Charlie[/TD]
[TD]Charlie EEEE[/TD]
[TD]45, jjjjjjj Blvd[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]No[/TD]
[TD]Dan[/TD]
[TD]Dan GGGGGG[/TD]
[TD]89, abc Dr,[/TD]
[/TR]
</tbody>[/TABLE]

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