Compare changes from multiple sheets and shown in New sheet.

[GeorgeAB]

Jindon,
I want sheet name its show up in the sheet names in Drop down list and Column E (Changes sheet on each row) which ever changes shows if I add any new sheet it should show up automatically in the drop down list..

PFA image.
Regards
GeorgeAB

 

[jindon]

Is there any meaning of doing this? or I must be missing something...
Change "test" to

Sub test()
     Dim ws1 As String, ws2 As String, rng As String, msg As String
     Dim x, i As Long, ii As Long, flg As Boolean
     With Application
         .EnableEvents = False: .ScreenUpdating = False
     End With
     With Me
         ws1 = .[h5]: ws2 = .[h6]
         If (ws1 = "") + (ws2 = "") Then Exit Sub
         If Not IsSheetExists(ws1) Then msg = ws1 & " is missing"
         If Not IsSheetExists(ws2) Then msg = msg & vbLf & ws2 & " is missing"
         If Len(msg) Then MsgBox msg: Exit Sub
         .Cells(1).CurrentRegion.Resize(, 4).Clear
         Sheets(ws1).Cells(1).CurrentRegion.Copy .Cells(1)
         With .Cells(1).CurrentRegion.Resize(, 4)
             .FormatConditions.Add 2, Formula1:="=r[0]c[0]<>'" & ws2 & "'!r[0]c[0]"
             .FormatConditions(1).Interior.Color = vbRed
             x = Evaluate("if(row(1:" & .Rows.Count & "),if(" & .Address & "<>'" & _
             ws2 & "'!" & .Address & ",row(" & .Address & "),""""))")
             For i = UBound(x, 1) To 2 Step -1
                 For ii = 1 To UBound(x, 2)
                     If x(i, ii) <> "" Then flg = True: Exit For
                 Next
                 If Not flg Then .Rows(i).Resize(, 5).Delete xlShiftUp
                 flg = False
             Next
         End With
         With .Cells(1).CurrentRegion
            If .Rows.Count > 1 Then
                With .Offset(1).Columns(5).Resize(.Rows.Count - 1)
                    .Value = Evaluate("if(mod(row(" & .Address & "),2),h6,h5)")
                End With
             End If
         End With
     End With
     With Application
         .EnableEvents = True: .ScreenUpdating = True
     End With
End Sub

Note: Code has been edited.

 

[GeorgeAB]

Thank u so much Jindon, this is what I was looking for...

Appreciate your help

Regards
GeorgeAB