Upload workbook to main macro and compare sheets for duplicate

Question

I am creating a macro that will have a verify button. Which means, I will upload an existing workbook (same sheets with the main macro). And I need to compare the wb1 and wb2 sheets if there are any duplicates and will eventually highlight the duplicate item on the sheet of main macro. So far this is what I have now but it doesn't allow me on the Set WorkRng1 = Wb1.Sharepoint.Range("A" & Sharepoint.Rows.Count).End(xlUp).row part. Here is my code below:

Sub UploadandCompareSheets()

Dim Wb1 As Workbook
Dim wb2 As Workbook
Dim MainPage As Worksheet
Set MainPage = Sheets("Main")
Dim tbl As ListObject
Dim ws1 As Worksheet
Dim Sharepoint As Worksheet
Set Sharepoint = Sheets("PRP Sharepoint")



Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set Wb1 = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a File", _
filefilter:="Excel File *.xlsx (*.xlsx),")

If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"

Exit Sub

Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)

For Each sheet In wb2.Sheets

    If sheet.Visible = True Then

      Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range

 Set WorkRng1 = Wb1.Sharepoint.Range("A" & Sharepoint.Rows.Count).End(xlUp).row
 Set WorkRng2 = wb2.Sharepoint.Range("A" & Sharepoint.Rows.Count).End(xlUp).row
 For Each Rng1 In WorkRng1
rng1Value = Rng1.value
For Each Rng2 In WorkRng2
    If rng1Value = Rng2.value Then
        Rng1.Interior.Color = VBA.RGB(255, 0, 0)
        Exit For
    End If
Next
Next



    End If

Next sheet

End If
End Sub

Jane Jane · Accepted Answer · 2017-03-03T14:15:07

You are setting an object called SharePoint:

Set Sharepoint = Sheets("PRP Sharepoint")

You're then trying to access this for 2 different workbooks:

Set WorkRng1 = Wb1.Sharepoint.Range("A" & Sharepoint.Rows.Count).End(xlUp).row
Set WorkRng2 = wb2.Sharepoint.Range("A" & Sharepoint.Rows.Count).End(xlUp).row

You can't do this, because SharePoint is not a method of the workbook object. When you create the SharePoint object initially, it will refer to a specific sheet (the one in the ActiveWorkbook if you don't specify which one). You'll either need to create 2 separate Sheet objects (after opening the workbooks):

Set Sharepoint1 = Wb1.Sheets("PRP Sharepoint")
Set Sharepoint2 = Wb2.Sheets("PRP Sharepoint")

or refer directly to the sheet name when declaring the range:

Set WorkRng1 = Wb1.Sheets("PRP Sharepoint").Range("A" & Sharepoint.Rows.Count).End(xlUp).row
Set WorkRng2 = wb2.Sheets("PRP Sharepoint").Range("A" & Sharepoint.Rows.Count).End(xlUp).row

Upload workbook to main macro and compare sheets for duplicate

1 Answers