File Compare Macro

**JoanneA** · 05-23-2013, 02:26 PM

I have seen posts referring to a file compare macro. I need to compare two files, one new and one old, and add 2 tabs to the new one that shows the items added on one tab, and the items deleted on the other, with respect to the old file. Can someone help me, please?

**p24leclerc** · 05-26-2013, 03:44 PM

Certainly. Can you attached sample files to you thread so we can work this out? Please give some instructions on how to compare both files.
Regards

**JoanneA** · 05-27-2013, 02:14 PM

**p24leclerc** · 05-27-2013, 10:02 PM

Here is a macro and 2 files that could help you.
The macro is based on the file's names and sheets' names also. If you have to change this, do not forget to modify the code accordingly.
The macro is in New-file.xlsm workbook. The Old-file.xlsm workbook has no macro in it and only one sheet of data. Both workbooks should be open before running the macro.
You start the macro when you are in the New-file sheet.
Not knowing waht to do with the ADDED or REMOVED jobs, I left them in place but they can also be deleted.

Public Sub Check_Files()
Dim Sh_New As Worksheet, Sh_Old As Worksheet, C_ell As Range, To_Copy As Range, F_ound As Range
Dim Sh_added As Worksheet, Sh_Removed As Worksheet
Set Sh_New = ActiveSheet
Set Sh_Old = Workbooks("Old-File.xlsm").Sheets(1)
'
'Check if Jobs Added sheet exists
On Error Resume Next
Set Sh_added = Sheets("Jobs Added")
On Error GoTo 0
If Sh_added Is Nothing Then
  Sheets.Add After:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Select
  Sheets(Sheets.Count).Name = "Jobs Added"
End If
Set Sh_added = Sheets("Jobs Added")

'Check if Jobs Removed sheet exists
On Error Resume Next
Set Sh_Removed = Sheets("Jobs Removed")
On Error GoTo 0
If Sh_added Is Nothing Then
  Sheets.Add After:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = "Jobs Removed"
End If
Set Sh_Removed = Sheets("Jobs Removed")
'
'Look for JOBS ADDED
Sh_New.Activate
For Each C_ell In Range("B2", Cells(Rows.Count, 2).End(xlUp))
  Set F_ound = Sh_Old.Columns(1).Find(C_ell)
  If F_ound Is Nothing Then
    If To_Copy Is Nothing Then
      Set To_Copy = C_ell.EntireRow
    Else
      Set To_Copy = Union(To_Copy, C_ell.EntireRow)
    End If
  End If
Next
If Not To_Copy Is Nothing Then
  To_Copy.Copy
  Sh_added.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
End If
Set To_Copy = Nothing
'
'Look for JOBS REMOVED
Sh_Old.Activate
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
  Set F_ound = Sh_New.Columns(2).Find(C_ell)
  If F_ound Is Nothing Then
    If To_Copy Is Nothing Then
      Set To_Copy = C_ell.EntireRow
    Else
      Set To_Copy = Union(To_Copy, C_ell.EntireRow)
    End If
  End If
Next
If Not To_Copy Is Nothing Then
  To_Copy.Copy
  Sh_Removed.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
End If

End Sub

Hope this helps

**JoanneA** · 05-28-2013, 11:47 AM

This is great. Thank you. There is only one thing that I need to add. This has to be done only if Column A has NPPAYROL, otherwis it should be skipped. If you can tell me how it should be coded, I can add it.

Again, thank you!

**p24leclerc** · 05-28-2013, 04:50 PM

Bonjour Joanne,
Sorry, I knew I missed something but couldn't tell what exactly.
Here is the modification to make. I thought it would be easier to change the whole JOBS ADDED section:

'Look for JOBS ADDED
Sh_New.Activate
For Each C_ell In Range("B2", Cells(Rows.Count, 2).End(xlUp))
  Set F_ound = Sh_Old.Columns(1).Find(C_ell)
  If F_ound Is Nothing And C_ell.Offset(0, -1) = "NPPAYROL" Then 'HERE IS THE MODIFICATION
    If To_Copy Is Nothing Then
      Set To_Copy = C_ell.EntireRow
    Else
      Set To_Copy = Union(To_Copy, C_ell.EntireRow)
    End If
  End If
Next
If Not To_Copy Is Nothing Then
  To_Copy.Copy
  Sh_added.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
End If
Set To_Copy = Nothing
'

Regards

**JoanneA** · 05-28-2013, 05:05 PM

Thank you, Pierre. I will make that change.

**JoanneA** · 05-28-2013, 06:12 PM

I ran it and got good results, then realized I needed to add a field before rerunning it. That field is ID, where each NPPAYROL job is numbered HR001, HR002, etc.
I reset all the files, re-added the NPPAYROL section to the macro, and now I can't get it to run again. These are the errors I get:

Run-time error '9':
Subscript out of range.

And when I hit Debug, it highlights the line:

Set Sh_Old = Workbooks("Old-File.xlsm").Sheets(1)

I don't know what I did to it, but can you help, please?

**p24leclerc** · 05-28-2013, 08:47 PM

this means Excel cannot find the workbook "Old-file.xlsm". Are you sure this workbook was open when you ran the macro? If you still have the same error, can you attached the 2 files so I can take a look?

**JoanneA** · 05-29-2013, 12:55 PM

Thank you, Pierre. I can't believe it was as simple as that. I thought the macro had to open the Old-file.xlsm, so I didn't have it open. I tried it again with it open, and it runs perfectly. Sorry to be such a pest.

File Compare Macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Re: File Compare Macro

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions