Compare changes from multiple sheets and shown in New sheet.

GeorgeAB · Feb 6, 2016

Hello Ninjas,

Good Afternoon,

I am new to this forum and this is my first thread, I hope I will get best solution from you.

I need help, I have a 7 sheets I need to compare the data ( Any cells data change ) from Wk1 V/s Wk2 or Wk3 V/s Wk5 and if there is a change its should shown up in the changes sheet, is it possible...

If sheet not found I need to get pop up stating that "Sheets not found"

I have attached sample file, I have put very less data. I have created dropdown list in Changes sheet.

I hope in this forum its possible. Please help me, I m tracking this one on manually.

Hope this is helps.

Thankyou,

George AB

vletm · Feb 6, 2016

@GeorgeAB
Something like this ...

GeorgeAB · Feb 6, 2016

Thanks for your response, Looks like similar.

Vletm, I want to compare the Sheet by sheet. If I want to look what are the changes in WK2 I want to compare Wk1 , but the result I want to see is only WK2, if I want to WK 2 comparsion against wk4 - I want to see only what are the changes happen in WK4 against wk2.

Importantly I missed to mentioned in the main thread I want one more column that is the sheet name which changes happen, so its very useful to me for view.

Thanks
GeorgeAB

GeorgeAB · Feb 6, 2016

Vletm,

I m really very sorry, its looks absolutely good.. Thank you so much

Kindly add one column which tell sheet name and if I add any new sheet its automatically add in to the data validation list.

Regards,
GeorgeAB

vletm · Feb 6, 2016

@GeorgeAB
Next version ... for checking

GeorgeAB · Feb 6, 2016

You are gr8 my dear... this is what I was looking for.

Waiting for Dropdown list same as sheet names....

You made my day man...

Thanks
GeorgeAB

vletm · Feb 6, 2016

@GeorgeAB
What are You waiting ...?
Did You tried to Add or Delete some TABs? If not, test to do so!
I add one more function, Activate any E-column's Sheet like Wk1,
then You'll move to that place...

GeorgeAB · Feb 6, 2016

OH no... Just I did... its done..

Thank you so much Vletm..

And one more thing , I am very new to this forum, this is my first thread its successful, I m really very happy.

What is the next step after complete this thread..?

Please let me know.

Regards,
GeorgeAB

jindon · Feb 6, 2016

Simple Conditional formatting

Code:

Sub test()
    Dim ws1 As String, ws2 As String, msg As String
    With Sheets("changes")
        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.Clear
        Sheets(ws1).Cells(1).CurrentRegion.Copy .Cells(1)
        With .Cells(1).CurrentRegion
            .FormatConditions.Add 2, Formula1:="=r[0]c[0]<>'" & ws2 & "'!r[0]c[0]"
            .FormatConditions(1).Interior.Color = vbRed
        End With
    End With
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

vletm · Feb 6, 2016

@jindon
Yes ... colors good!
but why You show all rows, not only changed?
And that was only one part of this case.
As always, there are many possibilities to do.

vletm · Feb 6, 2016

@GeorgeAB
You wrote:
What is the next step after complete this thread..?
Please let me know.
Question: What do You mean? Let me know too.

jindon · Feb 6, 2016

vletm

You don't need to care about solution from someone else.
I just showed OP a simple solution without loop.
If OP doesn't like it, he/she can ignore.

So simple.

vletm · Feb 6, 2016

@jindon
Okay, So simple,
Color part was good, as I wrote too.

GeorgeAB · Feb 7, 2016

Thanks for both... Happy weekend..
I loved it...

Regards
GeorgeAB

GeorgeAB · Feb 7, 2016

Hi Jindon,

Yours attachment is interesting.. Can u please help me with attachment which replied by Vletm,
Its shows more details.
Your color and Loops looks very help full. I need to look like Vletm attachment.

As mentioned from Vletm, why You show all rows, not only changed?
Your format and coding is fantastic. But I need only changed rows.
Can u please change the scenario which is uploaded final file by vletm.

Regards
GeorgeAB

GeorgeAB · Feb 7, 2016

jindon said:
vletm

You don't need to care about solution from someone else.
I just showed OP a simple solution without loop.
If OP doesn't like it, he/she can ignore.

Hi Jindon,

Yours attachment is interesting.. Can u please help me with attachment which replied by Vletm,
Its shows more details.
Your color and Loops looks very help full. I need to look like Vletm attachment.

As mentioned from Vletm, why You show all rows, not only changed?
Your format and coding is fantastic. But I need only changed rows.
Can u please change the scenario which is uploaded final file by vletm.

Regards
GeorgeAB

So simple.

jindon · Feb 7, 2016

GeorgeAB

If you upload a workbook with your desired result, I will be working on it.

GeorgeAB · Feb 8, 2016

HI Jindon, Thanks for your reply,

PFA sheet for your perusal.

I need below multiple changes required.

1. All the sheets , Any changes happen there is a unwanted data is coming up at Column F to K when I run this
code. Can u please remove this. But in your sheet I have not seen that one.
2. I want to add some more columns into all the sheet, can u please change the code, if columns it would automatically accept.

Rest data which is modified by Vletm, its absolutely fine.

Let me know If any more information required.

Regards,
GeorgeAB

jindon · Feb 8, 2016

Try replace my code with

Code:

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 Sheets("changes")
        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.Clear
        Sheets(ws1).Cells(1).CurrentRegion.Copy .Cells(1)
        With .Cells(1).CurrentRegion
            .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).Delete xlShiftUp
                flg = False
            Next
        End With
    End With
    With Application
        .EnableEvents = True: .ScreenUpdating = True
    End With
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

GeorgeAB · Feb 8, 2016

Hi Jindon,

Wow, excellent.......The code is working exactly what I want. But I need Sheet name in "E" Column ( Changes sheet ) like same sheet which i have attached previously and if I add or remove any sheet , the sheet name should automatically added into the drop down list. "H5 & H6".

Please do the needful.

Regards,
GeorgeAB

jindon · Feb 8, 2016

Sheet name?

How do you want it?

GeorgeAB · Feb 8, 2016

Jindon,

Please find attached XL file ( Sheet name Changes), Data validation. I need same like this.

Regards,
George AB

jindon · Feb 8, 2016

1) To ThisWorkbook Code module

Code:

Private Sub Workbook_Open()
    Run Sheets("changes").CodeName & ".worksheet_activate"
End Sub

2) Sheets("changes") code module

Code:

Option Explicit

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
     End With
     With Application
         .EnableEvents = True: .ScreenUpdating = True
     End With
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
     On Error Resume Next
     IsSheetExists = Len(Sheets(txt).Name)
     On Error GoTo 0
End Function

Private Sub Worksheet_Activate()
    Dim ws As Worksheet, myStr As String
    For Each ws In Worksheets
        If ws.Name Like "*Wk*" Then myStr = myStr & IIf(myStr <> "", ",", "") & ws.Name
    Next
    With Me.[h5:h6]
        If (myStr <> .Cells(1).Validation.Formula1) + (myStr <> .Cells(1).Validation.Formula1) Then
            .Validation.Delete
            .Validation.Add 3, , , myStr
        End If
    End With
End Sub

GeorgeAB · Feb 8, 2016

Jindon,

Coding is working fine.. but I m not able seen the Sheet name at "E" Column, at Changes Sheet.

If I add any new sheet its not shown up in the datavalidation from select week list.

Rest everything is fine...

Regards,
GeorgeAB

jindon · Feb 8, 2016

I said

jindon said:
Sheet name?

How do you want it?

And your attachment was like that.. Just heading...

Compare changes from multiple sheets and shown in New sheet.

New Member

Attachments

Excel Ninja

Attachments

New Member

Attachments

New Member

Excel Ninja

Attachments

New Member

Excel Ninja

Attachments

New Member

Well-Known Member

Attachments

Excel Ninja

Excel Ninja

Well-Known Member

Excel Ninja

New Member

New Member

New Member

Well-Known Member

New Member

Attachments

Well-Known Member

New Member

Well-Known Member

New Member

Attachments

Well-Known Member

Attachments

New Member

Well-Known Member