Help with code please

[Wes_A]

Firstly thank you all for your patience and valued assistance, it is really
appreciated.

I have the following code which does not find the record to paste into.
Maybe there is an easier way? Or can someone assist in finding what is wrong
here?
The macro is activated with the sheet "SCHOOL1_FEES.xlsm" active and the
workbook "SCHOOL_Account_Codes.xlsm" in memory.

'
Sheets("Sheet1").Select
Application.ScreenUpdating = False
Windows("SCHOOL1_FEES.xlsm").Activate
Sheets("INPUT").Activate
Range("L8").Activate 'Starting point for offsets
ActiveCell.Offset(0, -2).Select
Selection.Copy 'Content of cell J8
ActiveCell.Offset(0, 2).Activate

Dim varRange As Range
Dim varFound As Variant, varSearch As Variant
Windows("SCHOOL1_FEES.xlsm").Activate
Sheets("INPUT").Activate
varSearch = ActiveCell.Value 'Should be value in cell L8
Windows("SCHOOL_Account_Codes.xlsm").Activate
Sheets("School1").Activate
Set varRange = ActiveSheet.Range("B5:QV5")
Set varFound = varRange.Find(varSearch, lookat:=xlWhole)
If Not varFound Is Nothing Then varFound.Activate
ActiveCell.Offset(21, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'Should be contents of cell J8 above

Windows("SCHOOL1_FEES.xlsm").Activate
Sheets("INPUT").Activate

For i = 1 To Worksheets("Sheet1").Range("$A$4").Value


ActiveCell.Offset(1, 0).Activate 'Should now be cell L9 & on next "i" L10,
etc.
ActiveCell.Offset(0, -2).Select
Selection.Copy ' Should be content of cell J9
ActiveCell.Offset(0, 2).Activate
varSearch = ActiveCell.Value 'Should now be cell L9
Windows("SCHOOL_Account_Codes.xlsm").Activate
Sheets("School1").Activate
Set varRange = ActiveSheet.Range("B5:QV5")
Set varFound = varRange.Find(varSearch, lookat:=xlWhole)
If Not varFound Is Nothing Then varFound.Activate
ActiveCell.Offset(21, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'Should be contents of cell J9 above

Next i

Application.ScreenUpdating = True
Windows("SCHOOL1_FEES.xlsm").Activate
Sheets("INPUT").Activate

 

[JLGWhiz]

See if the modifications help.

Sheets("INPUT").Activate
varSearch = ActiveCell.Value 'Should be value in cell L8
Windows("SCHOOL_Account_Codes.xlsm").Activate
Sheets("School1").Activate
Set varRange = ActiveSheet.Range("B5:QV5")
Set varFound = varRange.Find(varSearch, lookat:=xlWhole)
If Not varFound Is Nothing Then varFound.Activate
ActiveCell.Offset(21, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'Should be contents of cell J8 above

Windows("SCHOOL1_FEES.xlsm").Activate
Sheets("INPUT").Activate
'<<<define values for variable i. Use i as row number in col L.
For i = 9 To Worksheets("Sheet1").Range("$A$4").Value
Range("L" & i).Offset(0, -2).Copy ' Should be content of cell J9
varSearch = Rang("L" & i).Value 'Should now be cell L9

Windows("SCHOOL_Account_Codes.xlsm").Activate
Sheets("School1").Activate
Set varRange = ActiveSheet.Range("B5:QV5")
Set varFound = varRange.Find(varSearch, lookat:=xlWhole)
If Not varFound Is Nothing Then varFound.Activate
ActiveCell.Offset(21, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'Should be contents of cell J9 above

Next i

Application.ScreenUpdating = True
Windows("SCHOOL1_FEES.xlsm").Activate
Sheets("INPUT").Activate

 

[JLGWhiz]

Noticed a typo. Change this:

varSearch = Rang("L" & i).Value 'Should now be cell L9

To this:

varSearch = Range("L" & i).Value 'Should now be cell L9

 

[Wes_A]

Thanks JLG.
I am getting an error on the line:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
with the message: "Compile error - expect named parameter"
What do I need to change to correct this?

 

[Wes_A]

Found a small typo and corrected it, so now have:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
and now get error:
Real Time Error '1004'
PasteSpecial method of Range class failed.