I want to compare Cell value from Column A IN active sheet to Column A in closed master workbook ( penn_990_migration_master_plan.xlsx), then once found similar cell in master sheet from Column A then grab corresponding cell value from C column from master workbook. Put in active sheet under column H.
Sub CompareAndCopy()
Dim NumberOfValues, NumberOfValues2 As Integer
Dim value1, value2 As String
Dim value3 As Long
Dim i, n, j As Long
Dim wb As Workbook
Dim wbsb1, wbsb2 As Worksheet
Set wb = Workbooks.Open("C:\excel\01synch\Penn_990_Migration_Master_Plan.xlsx")
Set wbws2 = wb.Sheets("Master")
NumberOfValues2 = wbws2.Range("A2").End(xlDown).Row
NumberOfValues1 = ThisWorkbook.Sheets("To Be migrated").Range("A2").End(xlDown).Row
Set wbws1 = ThisWorkbook.Sheets("To Be migrated")
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 1 To NumberOfValues1
value3 = 0
For n = 1 To NumberOfValues2
value1 = LCase(wbsb1.Range("A" & n).Value)
value2 = LCase(wbsb2.Range("A" & i).Value)
If Mid(value2, 1, Len(value1)) = value1 Then
value3 = value3 + wbsb2.Range("C" & n).Value
wbsb1.Range("H" & i).Value = value3
End If
Next
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
wb.Close
newExcel.Quit
Set newExcel = Nothing
End Sub
But getting message at "Value1 = Lcase(wbsb1.range("A" & n).value" run-time error '424' , object required. any idea what i am missing ??

