Copy/paste speed
 

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

Copy/paste speed
 

Change this segment:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste

To this:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)

You can put it all on one line by deleting the subscript (underscore) and
just leave a space between Copy and Sheets.

"Colin Macleod" wrote in message
...
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

Copy/paste speed
 

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)

This is a little more compact way to write the above line of code...

Cells(SelFactorRow, 6).Resize(SelFactorRow, 13).Copy Sheets("Report").Cells(r, 23)

--
Rick (MVP - Excel)



"JLGWhiz" wrote in message ...
Change this segment:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste

To this:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)

You can put it all on one line by deleting the subscript (underscore) and
just leave a space between Copy and Sheets.

"Colin Macleod" wrote in message
...
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

Copy/paste speed
 

Thanks for your help on this.


"Colin Macleod" wrote in message
...
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