Locating Info In New Excel Spreadsheet When Compared To Old One

[Internet Highway Traveler]

Hello,

I'm a neophyte regarding Excel spreadsheets, so the answer to my question
may be obvious to those experienced in it.

On a semi-weekly basis, I receive an Excel spreadsheet that is identical in
every way to the previous one except that new cells have been added.

Neither I nor the sender of the spreadsheet has any control over where the
new cells will be added within the file.

With some files having over 22,000 cells, it's extremely difficult and
time-consuming to visually search for those cells which are new.

Each cell, whether new or old, has info, including dollar amount.

It's the dollar amount in each of the new cells in which I am interested.

If it exists, I am looking for a method or software app that can compare
yesterday's spreadsheet "A" to tomorrow's spreadsheet "B" and then tell me
what appears in "B" that's not in "A".

As always, any and all constructive suggestions are appreciated.

Thank you.

IHT

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Otto Moehrbach]

The activeworkbook is the new workbook and it must contain this macro. The
name of the old workbook is assumed to be A.xls. The sheet in the old
workbook is assumed to be "General. Change these as needed. This macro
adds a new sheet, "Compare", to the active workbook. It then compares
every cell in the new workbook's used range against the same cell in the old
workbook. Any cell value that is different between the 2 workbooks will be
listed in the Compare sheet, address and value. HTH Otto
Sub Compare()
Dim Sh As Worksheet
Dim wb As Workbook
Dim i As Range
Dim Dest As Range
Set Sh = ActiveSheet
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "Compare"
Set Dest = Range("A1")
Sh.Select
Set wb = Workbooks("A.xls")
For Each i In ActiveSheet.UsedRange
If i <> wb.Sheets("General").Range(i.Address) Then
Dest = i.Address(0, 0)
Dest.Offset(, 1) = i.Value
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

 

[SanCarlosCyclist]

 The activeworkbook is the new workbook and it must contain this macro. The
name of the old workbook is assumed to be A.xls.  The sheet in the old
workbook is assumed to be "General.  Change these as needed.  This macro
adds a new sheet, "Compare", to the active workbook.   It then compares
every cell in the new workbook's used range against the same cell in the old
workbook.  Any cell value that is different between the 2 workbooks will be
listed in the Compare sheet, address and value.  HTH  Otto
Sub Compare()
    Dim Sh As Worksheet
    Dim wb As Workbook
    Dim i As Range
    Dim Dest As Range
    Set Sh = ActiveSheet
    Sheets.Add before:=Sheets(1)
    Sheets(1).Name = "Compare"
    Set Dest = Range("A1")
    Sh.Select
    Set wb = Workbooks("A.xls")
    For Each i In ActiveSheet.UsedRange
        If i <> wb.Sheets("General").Range(i.Address) Then
            Dest = i.Address(0, 0)
            Dest.Offset(, 1) = i.Value
            Set Dest = Dest.Offset(1)
        End If
    Next i
End Sub

"Internet Highway Traveler" <[email protected]>
wrote in message













- Show quoted text -

Hi Otto, I tried this and got a Run-Time error '9': Subscript out of
range.
How do I fix this. Your macro looks like a nift tool that I could use.

 

[Otto Moehrbach]

"Subscript out of range" means that the code is looking for something, a
sheet maybe, that doesn't exist. The only thing this macro is looking for
is the "Compare" sheet. Make sure you have one (or change the name in the
code). If you have one and still get the error, check the spelling of it
and/or check that the sheet name doesn't have an extra space before or after
it. Otto