Difference between Two sheets in two different workbooks

[Sumit Dey]

Hi All Good Evening,

I have came across an problem to which I am unable to find solution need your expertise to help me out.

I have two workbooks with around 60-70 tabs each (tabs are named similar in both the workbooks), I need to find difference between two tabs from both the workbooks ( which have same name).

Say both the workbooks have a Tab (Sheet) named "FTSE100" and have certain data in them. I want to know the difference between them. Both the sheets have a particular column named "Returns" I have to find the difference between them. It also have another column with unique identifier so that I can apply Vlookup to get the desired results.
But, the issue is there are more than 60 tabs (sheets) in each workbook and it will take a hell lot of time to apply Vlookup in each of the tabs (sheets).
I need that a VBA code creates a new sheet and shows results for each of the Sheets between both the workbook.

I hope I have clearly mentioned what I am looking for here.??

1. Workbooks are named Prt_Oct 2019 and Prt_Nov 2019.
2. both books will be open (suggest if it will work when one remain closed).
3. Prt_Nov 2019 should have the results in it.
4. Yes the first row have headers.
5. 1st col have the unique identifier ( Col header named ISIN).
6. Col E will have Returns.
7. Columns headers are consistent.

 

[davesexcel]

What kind of differences are you looking for?

 

[Sumit Dey]

There are about 60-70 sheets in both the workbooks each sheet is named (same name in both the workbooks).
Each sheet have a column that have some numerics viz, return (Col E for e.g) I want that a separate worksheet gets created in a workbook and show me the difference between "Col E" in each sheet between the workbooks.

Column A in each sheet have unique identifiers so, that we can use Vlookup to get the numbers but, we have to apply Vlookup in each of the 60+ sheets each month so, if there's any VBA code that can automate this will be a great help.

 

[davesexcel]

Would it be safe to say, you require the differences between the unique identifiers in column a to the values in column E. If there is a difference show that in a separate worksheet. So you would need the worksheet name, the unique id and the difference.

 

[davesexcel]

Loop through the sheets and find differences
You can edit the code to return what you want, right now the results will just show false
Only the items that don't equal will show up as results.
Create a sheets Named Results in your Nov workbook, the results will end up there and that is the workbook with the code.

-

The code

Sub GetDifs()
    Dim wb As Workbook, bk As Workbook
    Dim ws As Worksheet, sh As Worksheet, rw As Long
    Dim LstRw As Long, c As Range, rng As Range, FdRng As Range


    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Results")
    Set bk = Workbooks("Prt_Oct 2019.xlsx")

    For Each sh In bk.Sheets
        With sh
            LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
            Set rng = .Range("A1:A" & LstRw)
            For Each c In rng.Cells
                Set FdRng = wb.Sheets(sh.Name).Columns(1).Find(what:=c, lookat:=xlWhole)
                If Not FdRng Is Nothing Then
                    If c.Offset(, 4).Value <> FdRng.Offset(, 4).Value Then
                        With ws
                            rw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                            .Cells(rw, 1) = sh.Name
                            .Cells(rw, 2) = c
                            .Cells(rw, 3) = c.Offset(, 4).Value = FdRng.Offset(, 4).Value
                            '.Cells(rw, 4) = c.Offset(, 4).Value - FdRng.Offset(, 4).Value
                        End With
                    End If
                End If
            Next c
        End With
    Next sh
End Sub

 

[Sumit Dey]

Each sheet is looks something like the attached. I don't have access to Excel right now so, I tried sharing a screenshot on my phone.
The difference should be of the Column E "Return". Each sheets are named similar in both the Workbooks.

 

[Sumit Dey]

Loop through the sheets and find differences
You can edit the code to return what you want, right now the results will just show false
Only the items that don't equal will show up as results.
Create a sheets Named Results in your Nov workbook, the results will end up there and that is the workbook with the code.

-
View attachment 1517

The code

Sub GetDifs()
    Dim wb As Workbook, bk As Workbook
    Dim ws As Worksheet, sh As Worksheet, rw As Long
    Dim LstRw As Long, c As Range, rng As Range, FdRng As Range


    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Results")
    Set bk = Workbooks("Prt_Oct 2019.xlsx")

    For Each sh In bk.Sheets
        With sh
            LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
            Set rng = .Range("A1:A" & LstRw)
            For Each c In rng.Cells
                Set FdRng = wb.Sheets(sh.Name).Columns(1).Find(what:=c, lookat:=xlWhole)
                If Not FdRng Is Nothing Then
                    If c.Offset(, 4).Value <> FdRng.Offset(, 4).Value Then
                        With ws
                            rw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                            .Cells(rw, 1) = sh.Name
                            .Cells(rw, 2) = c
                            .Cells(rw, 3) = c.Offset(, 4).Value = FdRng.Offset(, 4).Value
                            '.Cells(rw, 4) = c.Offset(, 4).Value - FdRng.Offset(, 4).Value
                        End With
                    End If
                End If
            Next c
        End With
    Next sh
End Sub

Sorry but I am a novice and could got anything.. I applied the code and the results are similar to what you have said " False"
I don't understand how to make it work

 

[davesexcel]

What kind of results are you looking for? I have a commented line that will subtract the two differences. I commented it out in case the cells were not numeric.

 

[Sumit Dey]

I am unable to attach excel file so, that I can better explain it to you...

I have two workbooks say "Testing_1" and "Testing_2" and multiple worksheets in each of them (each sheet named Similar) in both the Workbooks.
The sheets have 5 columns A to E. Col A have unique identifiers and Col E have numerics. I want to fetch the differences in Col E between two Similar sheets in two workbooks. I can manually apply Vlookup in each sheets to get the results but, it's time consuming to do it.
I wonder if there is any code wherein this gets automated. The results could be in a new sheet or to a column next to col E in each sheet.
Please see the attached snippet of it can help.

Thanks in advance

 

[davesexcel]

Look at the code
remove the apostrophe from this line
'.Cells(rw, 4) = c.Offset(, 4).Value - FdRng.Offset(, 4).Value