Looking to Make Macro More Efficient

[WindsorKnot]

Hi all,

I copied a snipet of my code below. I need to simply determine how to make this process a little faster, as I have to do this for 30 subdivisions. More specifically, is there a more effiecent means to doing the pastespecial arguement, as I think this may speed things up a bit

Thanks

Sub RateExhibits_Update()
 
Application.ScreenUpdating = False
 
' Update Total Exhibit
    Sheets("Rate Summary-Updated").Range("D4:G76") _
    .Copy Sheets("Total").Range("D20").Select
    Sheets("Total").Range("D20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Application.CutCopyMode = False
End Sub

 

[jmattus]

This is a much easier way. Don't use the copy command at all. Simple code the values. Something like this:

Sheets("Total").Range("D20:G92").values = Sheets("Rate Summary-Updated").Range("D4:G76").values

In this example you need to make sure that the ranges are the same size.

JM

 

[WindsorKnot]

Awesome you just saved me from a 2 amer

 

[WindsorKnot]

Jmattus,

For some reason I can't get your formula to work

This is how I have altered it slightly

Sheets("Total").Range("D4:G76").Values = _ 
Sheets("Rate Summary-Updated").Range("BV4:BY76").Values

I get the runtime error: Object doesn't support this method or property

Can you please tell me where I am going wrong

 

[yytsunamiyy]

Jmattus,

For some reason I can't get your formula to work

This is how I have altered it slightly

Sheets("Total").Range("D4:G76").Values = _ 
Sheets("Rate Summary-Updated").Range("BV4:BY76").Values

I get the runtime error: Object doesn't support this method or property

Can you please tell me where I am going wrong

Its always range().VALUE - without the -s. So your code should be:

Sheets("Total").Range("D4:G76").Value = _ 
Sheets("Rate Summary-Updated").Range("BV4:BY76").Value

 

[WindsorKnot]

Problem solved

 

[jmattus]

That's right, yytsunamiyy. Sorry for the confusion. Good catch.

JM