+ Reply to Thread
Results 1 to 5 of 5

Find differences between sheets in two different workbooks

  1. #1
    Registered User
    Join Date
    03-25-2013
    Location
    New Delhi
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    37

    Find differences between sheets in two different workbooks

    Hi experts,

    I need some urgent help with this.
    I have two workbooks with many sheets in them which are named similar.
    I want to get the difference between a particular column between each sheet in the workbooks I have attached the workbook for your reference. In my example I need to find the difference between column E and the results should show up in a new worksheet.
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,743

    Re: Find differences between sheets in two different workbooks

    I need some urgent help with this.
    Administrative Note:
    Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

  3. #3
    Registered User
    Join Date
    03-25-2013
    Location
    New Delhi
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    37

    Re: Find differences between sheets in two different workbooks

    Sub MakeFormulas()
    Dim SourceLastRow As Long
    Dim OutputLastRow As Long
    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim outputSheet As Worksheet
    Application.ScreenUpdating = True

    'Where is the source workbook?
    Set sourceBook = Workbooks.Open("C:\My Documents\Book1.xlsx")

    'what are the names of our worksheets?
    Set sourceSheet = sourceBook.Worksheets("Sheet1")
    Set outputSheet = ThisWorkbook.Worksheets("Sheet1")

    'Determine last row of source
    With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    With outputSheet
    'Determine last row in col P
    OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
    .Range("Q2:Q" & OutputLastRow).Formula = _
    "=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
    End With

    'Close the source workbook, don't save any changes
    sourceBook.Close False
    Application.ScreenUpdating = True
    End Sub

    I am using the above code but, it's showing Error 9 subscript out of range. The debugger is at " Set output sheet= ThisWorkbook.Worksheets("Sheet1").

    Please advice

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,782

    Re: Find differences between sheets in two different workbooks

    Aside from what Pepe told you, read the forum rules. Number 2 is of interest to you.

  5. #5
    Registered User
    Join Date
    03-25-2013
    Location
    New Delhi
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    37

    Re: Find differences between sheets in two different workbooks

    Please Login or Register  to view this content.
    I think this may work

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find differences between sheets in different workbooks
    By sumit dey in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2019, 04:35 PM
  2. How to find differences between two sheets?
    By maknu in forum Excel General
    Replies: 4
    Last Post: 05-13-2019, 08:52 AM
  3. Comparison of 2 sheets from different workbooks and highlighting the differences
    By zsimur in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2013, 07:31 AM
  4. Find Differences between columns sheets
    By dalerdd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2013, 02:44 PM
  5. how to find differences between 2 excel sheets
    By remdog in forum Excel General
    Replies: 7
    Last Post: 09-11-2012, 07:16 AM
  6. Replies: 1
    Last Post: 07-12-2012, 05:43 AM
  7. [SOLVED] Find differences between two Excel workbooks or worksheets
    By savage_planet in forum Excel General
    Replies: 0
    Last Post: 10-03-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1