Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Possible to compare data of two sheets with VBA?

    Hi Experts
    I am dealing with an issue where I have to compare the data between 2 sheets, basically the currency columns in the sheets. If they are fine then no issue, but if they are different then have to highlight it. Is there a way to make this process automate?
    Regards,
    JD

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Need more information as to where the data is located to compare. Is the format exactly the same? If so, something like this would work.

    Code:
    Public Sub CompareSheets()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cell As Range, rng As Range
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set rng = ws1.Range("A1:A20")
    For Each cell In rng
        Celladdress = cell.Address
        If cell <> ws2.Range(Celladdress) Then
            cell.Interior.Color = vbYellow
            ws2.Range(Celladdress).Interior.Color = vbYellow
        End If
    Next cell
        
    End Sub
    HTH,
    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jaggi

    ..it's possible to do everything in vba!

    When I want to do a quick check on what numbers are different on two similar-layout sheets, I use Ctrl-A to select an entire sheet, press Ctrl-C to copy it, then on the second sheet, in cell [A1], I use edit>pastespecial>subtract
    I can then see if there are non-zero values (which represents 'what's different' in the values).
    I can then use Ctrl-Z to undo the pastespecial.
    It's a quick non-vba method.

    zeddy
    I need more patience and I want it now

  4. #4
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    559
    Thanks
    51
    Thanked 68 Times in 66 Posts
    zeddy, I'm very interested in your technique.

    I can then see if there are non-zero values (which represents 'what's different' in the values).
    I've never used this particular method. However my initial reaction is that this wouldn't work. The problem is that pasting is always a simple process involving contiguous blocks of data. You see the most common application of spreadsheet comparison I do is to "find where rows have been added or removed".

    Since Excel uses simple pasting logic, it does no row matching. Therefore you cannot find excess or missing rows this way. At best you'll end up with a handful of rows past the point where the paste ended. And if the rows there are the mismatches that's just dumb lucky.

    So I have to ask: Are you assuming that the row counts between the two spreadsheets (or tabs) are identical?

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    Why not just record your steps in a macro?

    Recorded Macro using zeddy's steps:

    Code:
    Sub Macro1()
        Cells.Select
        Range("F3").Activate
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
            SkipBlanks:=False, Transpose:=False
    End Sub
    Then just run the code!

    Maud
    Last edited by Maudibe; 2015-09-07 at 19:19.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi BHarder

    Yes, in my quick method, the sheets are 'similar-layout' sheets i.e. identical row/column structures. That's why it's a 'quick non-vba method'! For a proper vba method, this is what I used:

    The data to be compared was held in 'month' files, eg in format [zzz@yyyy-mm.xlsx] where zzz might refer to some entity, for example a country, a region, a state etc etc. The datafiles would have multiple sheets. For example, column A rows might contain product items or account codes etc, the columns might contain Branches or Stores etc. Each of the sheets could be product Categories/Divisions etc etc.

    So, between one month and another, there could be additional product lines added (i.e. additional rows) and product lines discontinued (product rows removed), and there could be additional columns for new Stores etc etc.

    So part of the automated vba comparison process would require 'synchronisation alignment of the sheet structures' when comparing the Category/Divison sheets in the two data files. This is easily done using vba.

    For comparison, this could done on dual monitors with each data file in a separate Excel window i.e. two Excel sessions running. The vba comparison process would be managed via a separate Excel 'comparison tool'. The hardest part would be the vba controlling the 'other Excel session'. But it is doable. When you click on a sheet tab in file 1, the same sheet tab would be selected automatically in file 2. As you scrolled either up/down or left/right in file 1, this would be replicated in file 2. By using the 'pastespecial subtract' method under vba control, and 'hiding zeros', it is easy to see the 'differences'. This method could be used to compare 'before' and 'after' transfers between accounting systems etc etc.

    Good news: starting with Excel 2013, each of your workbooks now opens in its own window, similar to Word and PowerPoint today. This is called the Single Document Interface (SDI). That means that each workbook has its own ribbon and top-level window frame that you can move and resize independent from your other open workbooks. This functionality can improve multitasking, visibility, and analysis across workbooks.

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Use the macro recorder?
    Moi???

    zeddy

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Now zeddy, I think we all have to admit that we re-visit the macro recorder from time to time to refresh ourselves on how to use that not so often used task! I know you are a brain when it comes to vba but it is impossible to memorize the vastness of every object/property/method/parameter.

    Maud

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..I was just kidding - honest guv - you got me bang to rights - guilty as charged - it was me - I did it - use it all the time - can't live without it - I love using the macro recorder...

    ziddy

    ..someone else wrote this on my laptop while I was making tea
    zeddy
    Last edited by zeddy; 2015-09-08 at 10:36.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    559
    Thanks
    51
    Thanked 68 Times in 66 Posts
    I remember when the Multiple Document Interface (MDI) was a thing. Never thought I'd see the day when an SDI was considered an advantage!

    I wonder if MS can implement SDI with memory savings as the MDI used to do... or do they just not care now? Memory is so much more abundant and cheaper now than the old days. Still, I always hate to see when programmers get lazy and waste resources "just because".

    And yet I know that happens a lot now.

  11. #11
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post

    Hi All

    Thanks for your input on this thread.

    Maud – Your first code works fine, but when I am trying to make the range dynamic I am getting an error 1004 on the following line of code. Please lemme know what I am doing wrong here.

    “Set rng = ws1.Range("A1", Cells(LastRow, LastColumn))”

    Code:
    Public Sub CompareSheets()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cell As Range, rng As Range
    Dim LastRow As Long
    Dim LastColumn As Long
    
    Set ws1 = Worksheets("Sheet1")
    LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    LastColumn = Sheet1.Cells(2, Columns.Count).End(xlToLeft).Column
    
    Set ws2 = Worksheets("Sheet2")
    Set rng = ws1.Range("A1", Cells(LastRow, LastColumn))
    For Each cell In rng
        Celladdress = cell.Address
        If cell <> ws2.Range(Celladdress) Then
            cell.Interior.Color = vbYellow
            ws2.Range(Celladdress).Interior.Color = vbYellow
        End If
    Next cell
    End Sub

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Jaggi,

    You got the error because you had sheet 2 activated when running the code. To prevent this, have sheet 1 activated or better yet, change the line

    Set rng = ws1.Range("A1", Cells(LastRow, LastColumn))

    to

    Set rng = ws1.Range("A1", ws1.Cells(LastRow, LastColumn))

    You can have any sheet activated this way. I always assign the sheet in the address to prevent these errors.

    HTH,
    Maud

  13. #13
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud
    This looks fine, and it solves my purpose, but I was thinking something else now. Typical human tendency, Is it possible for the highlighted cell to change its color when I rectify the difference. Just a thought.
    Regards,
    JD

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Jaggi,

    Remove above code. Place the following code in sheet2 worksheet module:

    Code:
    Private Oldval As Variant
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Oldval And Target = Worksheets("Sheet1").Range(Target.Address) Then _
        Target.Interior.Color = vbYellow
    If Target <> Oldval And Target <> Worksheets("Sheet1").Range(Target.Address) Then _
        Target.Interior.Pattern = xlNone
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Oldval = Target
    End Sub
    When you select a cell on sheet 2, the cell value will be assigned to Oldval. If you change the cell value (no longer equals Oldval) to match the corresponding value in sheet 1, then the interior color will change to yellow.

    HTH,
    Maud
    Attached Files Attached Files

  15. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I hate to correct Zeddy, the all-knowing one, but you can do everything in VBA except get it to report when the internal colour of a cell changes.
    For those for whom the only data in the world is letters and numbers that is OK - for those of us who see a more polychrome world it's a disappointment.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •