Excel VBA compare worksheets

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).

Excel VBA compare worksheets 01

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.

Excel VBA compare worksheets 08

 

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

Excel VBA compare worksheets 06

 

Step 2 – Identify Workbooks in template

Write down the workbook names in the template.

Excel VBA compare worksheets 02

 

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.

Excel VBA compare worksheets 03

 

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.

Excel VBA compare worksheets

 

Step 4 – Run the Macro

Click on the button to kick start compaison

Excel VBA compare worksheets 04

 

Result

The differences are captured in worksheet “difference”.

Excel VBA compare worksheets 07

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

Outbound References

https://support.office.com/en-us/article/Basic-tasks-in-Spreadsheet-Compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

Leave a Reply

Your email address will not be published.