Compare columns across worksheets to ensure they match.

**CJPHX** · 06-07-2010, 05:02 PM

Good Afternoon all,
I currently have a macro that grabs some date form sheet 3 and pastes it onto sheet which feeds into a pivot table. The problem I am having is that if the worklists names get reorganized on one sheet the data is inaccurate when it is pasted over as the worklists names are not on the same rows. Is there a way to get the macro to check the columns to ensure the information is identical and showing an error message if not before continuing on.

For Example: Column A of sheet 1 has the alphabet
A
B
C
D

Column A of sheet 2 has the alphabet as well but it go out of order.
D
B
C
A

I would like the macro to check and make sure column A on both sheets has the same info in each row before copying whatever data I need.

**salimudheen** · 06-08-2010, 01:20 AM

Hi,
Below is the code for that:

Copy this code and paste in to one module.

And change the workbooks path.

Sub Test()
    Dim WBMain As Workbook
    Dim WBSub As Workbook
    
    Dim WSMain As Worksheet
    Dim WSSub As Worksheet
    
    Dim index As Long
    Dim strSearch As String
    
    'Open main workbook
    Set WBMain = Application.Workbooks.Open("C:\X.xls")
    'Open sub workbook
    Set WBSub = Application.Workbooks.Open("C:\Y.xls")
    
    Set WSMain = WBMain.Sheets(1)
    Set WSSub = WBSub.Sheets(1)
    
    'Read each row from main workbook
    'And search that string in to second workbook
    With WSMain
        For index = 1 To .UsedRange.Rows.Count
            strSearch = .Cells(index, "A")
            
            'Find is exists
            If IsExists(strSearch, WSSub) Then
                'Write the code what you want, if the value is exists
            End If
        Next index
    End With
End Sub

Function IsExists(strSearch As String, Ws As Worksheet) As Boolean
    Dim rngFind As Range
    With Ws
        Set rngFind = .Range("A:A").Find(what:=strSearch, LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext, searchorder:=xlByRows)
        
        If Not rngFind Is Nothing Then
            IsExists = True
        Else
            IsExists = False
        End If
    End With
End Function

Hope it will more help you!!!

Regards,