# Compare 2 workbooks

#### Steffmeister

##### Board Regular
Hi everybody,

Thirst I want to say that this forum is really great, keep up the good working .

I'm not really good in macros in Excel and I've got a question if somebody have a macro which compares 2 workbooks and the difference copy in an other workbook. It's like this:

- I got a workbook called FileA.xls and a worksheet SheetA. In column C stands a lot of numbers (like ip-addresses or something).
- The other workbook called FileB.xls and a worksheet SheetB. Here stands in column D also some numbers.
- I'd like to compare workbook FileB with FileA and put the data from FileB which are NOT in FileA in an other workbook.

Is this possible? If yes, can someone plz help me?

Tnx for now,
Grts Steffmeister

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Welcome to the Board.

Try this:

Code:
``````Sub Test()
Dim WF As WorksheetFunction
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Rng As Range
Dim WBNew As Workbook
Dim r As Long
Dim Cell As Range
Dim i As Long
Set WF = WorksheetFunction
Set Sh1 = Workbooks("FileA.xls").Worksheets("SheetA")
Set Sh2 = Workbooks("FileB.xls").Worksheets("SheetB")
Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)
r = 1
For Each Cell In Rng
On Error Resume Next
i = WF.Match(Cell.Value, Sh1.Range("C:C"), False)
If Err <> 0 Then
Err.Clear
WBNew.ActiveSheet.Cells(r, 1).Value = Cell.Value
r = r + 1
End If
On Error GoTo 0
Next Cell
End Sub``````

WAUWWW tnx for the fast answer, this is really GREAT.
Tnx a lot, it works perfect.

Grts Steffmeister

Sorry for the inconvenience but is it also possible to make a command button in a workbook and when I push the command button the comparation begins of FileA.xls and FileB.xls and put the difference in the same file with the command button?

Grts Steffmeister

Steffmeister said:
Sorry for the inconvenience but is it also possible to make a command button in a workbook and when I push the command button the comparation begins of FileA.xls and FileB.xls and put the difference in the same file with the command button?

Grts Steffmeister

Yes. Do you want the results on an existing worksheet or on a new worksheet?

On the existing worksheet plzz

Add a CommandButton from the Forms Toolbar and assign it this code:

Code:
``````Sub Test()
Dim WF As WorksheetFunction
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Rng As Range
Dim r As Long
Dim Cell As Range
Dim i As Long
Set WF = WorksheetFunction
Set Sh1 = Workbooks("FileA.xls").Worksheets("SheetA")
Set Sh2 = Workbooks("FileB.xls").Worksheets("SheetB")
Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)
ActiveSheet.Cells.Clear
r = 1
For Each Cell In Rng
On Error Resume Next
i = WF.Match(Cell.Value, Sh1.Range("C:C"), False)
If Err <> 0 Then
Err.Clear
ActiveSheet.Cells(r, 1).Value = Cell.Value
r = r + 1
End If
On Error GoTo 0
Next Cell
End Sub``````

It works FANTASTIC. Thank you very much

Grts Steffmeister

Hi Andrew Poulsom,

I got a new question, maybe you know the answer or somebody else on this forum?

I got 2 input files, FileA.xls and FileB.xls. But this can also be various, zo FileC.xls for example. Is it possible to change the filename in the excel sheet so that he knows which file he must get?
For example in cell D6 stands C:\FileC.xls and in cell D7 C:\FileA.xls
When I change cell D6 in C:\FileB.xls he must get FileB.xls.

And I prefered to not open the excel files manually. Now I must first open the files to compare.

Second question is:
There are a lot of numbers, is it possible to put a 'Stop' button in it?

Tnx by now.

Steffmeister

Try this:

Code:
``````Sub Test()
Dim WF As WorksheetFunction
Dim Sh As Worksheet
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Rng As Range
Dim r As Long
Dim Cell As Range
Dim i As Long
Set WF = WorksheetFunction
Set Sh = ActiveSheet
Set Sh1 = Workbooks.Open(Sh.Range("D7").Value).Worksheets(1)
Set Sh2 = Workbooks.Open(Sh.Range("D6").Value).Worksheets(1)
Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)
Sh.Columns(1).Cells.Clear
r = 1
For Each Cell In Rng
On Error Resume Next
i = WF.Match(Cell.Value, Sh1.Range("C:C"), False)
If Err <> 0 Then
Err.Clear
Sh.Cells(r, 1).Value = Cell.Value
r = r + 1
End If
On Error GoTo 0
Next Cell
Sh1.Parent.Close
Sh2.Parent.Close
End Sub``````

You can stop the Macro by pressing Esc or Ctrl+Break.

Replies
16
Views
528
Replies
85
Views
1K
Replies
17
Views
484
Replies
1
Views
268
Replies
2
Views
346

1,196,080
Messages
6,013,314
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back