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.

 

[Sumit Dey]

I have edited the code to suit my requirent:

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Application.ScreenUpdating = True

'Where is the source workbook?
Set sourceBook = Workbooks.Open("C:\My Documents\Book1.xlsx")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")

'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
'Determine last row in col P
OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
'Apply our formula
.Range("Q2:Q" & OutputLastRow).Formula = _
"=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With

'Close the source workbook, don't save any changes
sourceBook.Close False
Application.ScreenUpdating = True
End Sub

But, it shows Error 9 subscript out of range and the debugger at " Set output sheet= ThisWorkbook.Worksheets("Sheet1")".

I tried to look into this code line but didn't find anything wrong, please advice.

 

[davesexcel]

Is there a worksheet named "Sheet1"?

 

[Sumit Dey]

Is there a worksheet named "Sheet1"?

Yes the sheets are named as Sheet1, Sheet2,,,, in both the workbooks

 

[Sumit Dey]

Yes the sheets are named as Sheet1, Sheet2,,,, in both the workbooks

Hi davesexcel,

I have solved the issue with the code...
But, my code applies in a single worksheet only say sheet1 in Book1 vs Sheet1 in Book2 but, I have multiple sheets in each of the Books and I want that my code applies to each of the available sheets in the books and the output to be in Book2.
Could you please advice the required changes to my code for the same, please.