VBA Coding to Compare different workbooks

[vignesh_thegame]

Hi,

I have plenty of excels and each of them are paired. ( 2 workbooks for a project).

I need to compare both workbooks using vlook up formula.

I did this by recording macro, and it works for that pair of spreadsheet alone.

The issue is, it is recording the spreadsheet name, so if i use that macro for another pair of spreadsheet, its not working as that workbooks file name are different.

I need a help to run the vlookup with any two opened workbooks and should not worry about file name:

Compare Macro
'

'
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Data2.xlsx]Sheet1!C1:C3,3,0)"
Range("F2").Select
Selection.Copy
Range("F2").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet2").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Data2.xlsx]Sheet1!C1:C3,3,0)"
Range("F2").Select
Selection.Copy
Range("E2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
End Sub

I posted same files to below link:

Data1.xlsm and 1 more file

2 files sent via WeTransfer, the simplest way to send your files around the world

we.tl