Slow Macro Problem

MARKYB0Y

New Member
Joined
Dec 5, 2007
Messages
7
Hi All,

I have a large macro that copie over 300 ranges of cells from one worksheet to another worksheet in the same workbook, the whole thing take over 60 seconds to complete. I am looking for a way to speed up this process.

an example of one range copy is

Code:
    Sheets("scores").Select
    Range("BK7:BQ7").Select
    Selection.Copy
    Sheets("scoresheets").Select
    Range("C70").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I have been looking around and I came across this line that supposedly speeds up the copying by bypassing the clipboard

Code:
worksheetname.Range("A1:A6").Value = worksheetname.Range("A1:A6").Value

has anyone used this in the past? is it compatible with 2007?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Welcome to the board...

Yes, it's usually easier and more efficient to say

Range("A1") = Range("B1")
than it is to say
Copy Range("B1") and Paste it to Range("A1")

The only disadvantage to this is that only the VALUES get copied, not formats or formulas.
And that method works in all versions of excel.


But the biggest problem (causing slow performance) your code has is SELECTING...
You almost never need to SELECT a range or sheet to manipulate it..

And eliminating select from code is not difficult (tedious yes, but not difficult)...
Basic idea is to search your code for the word SELECTION. Anywhere you see that, you can just replace the word Selection with whatever was previously Selected. And you would need to add Specific Sheet Names To ranges.
given your example code,

It would be changed to

Code:
    Sheets("scores").Range("BK7:BQ7").Copy
    Sheets("scoresheets").Range("C70").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Hope this helps...
 
Last edited:
Upvote 0
The second code that you posted should work in XL 2007. However your first code will speed up if you eliminate all that selecting

Code:
Sheets("scores").Range("BK7:BQ7").Copy
Sheets("scoresheets").Range("C70").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Upvote 0
Hi Jon, Vog,

well if the single line should work then that will be great as I only need the values copied, not formatting etc.
however I cannot get it to run
I have just tried this macro

Code:
scores.Range("N2:T2").Value = scoresheets.Range("C365:I365").Value

run time error '424'
object required

when I debug the whole line is highlighted?
I am trying to copy N2:T2 on scores and paste into C365:I365 on scoresheet

any ideas?
 
Upvote 0
Try

Code:
Sheets("scoresheets").Range("C365:I365").Value = Sheets("scores").Range("N2:T2").Value
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top