Unable to copy data from another workbook while in working current sheet

Viewed 44

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 ??

1 Answers

You have a typo. What you want it to be ...

enter image description here

... what you've got/assigned ...

enter image description here

To help you in the future, put Option Explicit at the top of your module. That will ensure you've correctly initialised all variables.

Related