This Excel tutorial explains how to use Excel VBA to compare worksheets.
You may also want to read:
Excel compare Worksheets using Compare File
Excel compare worksheets
Compare worksheets is to compare worksheets of similar structures but different values. Excel has a built it function called Compare File in Office Professional Plus 2013 or Office 365 ProPlus. After comparison is complete, two worksheets are compared side by side and you get the below result indicating all the differences (change).
In other versions of Excel, you need to create a Macro to do the automated comparison.
Excel VBA compare worksheets
To compare worksheets is definitely not a difficult task in VBA, you just need to write an iteration to loop through all used Range and compare the values. The question is how automatic you want to make to facilitate the work. I have created a template to collect user requirement and I will explain the template below. Note that this is a very rough application to compare Text only, not formatting, formula, etc.
Preparation
Click here to download the template containing the Macro, open the workbook.
Step 1 – Open Workbooks
The very first thing to do is to identify to which workbooks the worksheets belong.
In the below example, each workbook contains two worksheets, the goal is to compare
- 2013 budget vs 2014 budget
- 2013 compensation vs 2014 compensation
Step 2 – Identify Workbooks in template
Write down the workbook names in the template.
Step 3 – Identify Worksheets
The next step is to identify which worksheets you want to compare within the above two workbooks, you can have multiple comparisons each time.
What if the workbook contains hundreds of worksheets? To facilitate the mapping, I created a button to get all worksheets automatically, but you may need to make manual adjustment for the mapping.
Step 4 – Run the Macro
Click on the button to kick start compaison
Result
The differences are captured in worksheet “difference”.
Column A and B are the Cell address that you are comparing. You can click on the hyperlink to link to the Cell.
Column C and D are the values in workbook 1 and workbook 2.
Column E and F are the headers of the cell values. Header here refers to the row 1 data value of the comparing Cell, of course there are occasions where the data does not have header or the header is not in row 1, then you can simply ignore these two columns