I'd appreciate some help in re-writing the following bit of
lookup/copy/paste code so that it runs faster. Typically, StartRow will have
a value of 10, and EndRow could be 4000 or 5000.

For r = StartRow To EndRow
If Application.IsNA(Cells(r, ChanCode_Col)) = False Then
SelChanCode = Cells(r, ChanCode_Col)
RegSht.Select
SelFactorRow = Application.Match(SelChanCode, Range("b:b"), 0)
Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste
End If
Next

Thanks very much for any help and suggestions.


Colin