How to make Macro quicker and shorter

[SaraB802]

So here is one that no doubt those VBA gurus amoung you can solve quickly. My code does a simple copy and paste funtion for a formula then overwrites it with a copy paste values. Below is the sample:

Sub Update_Actuals()
'
' Update_Actuals Macro
' Updates Actuals on Resources Data worksheet
'


'
    Range("R7").Select
    Selection.Copy
    Range("S7:KF7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("R10").Select
    Selection.Copy
    Range("S10:KF10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("R13").Select
    Selection.Copy
    Range("S13:KF13").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

So the above covers 3 rows, I need to do it for over 750 rows and really don't want to have to repeat this for all of them. So far I have got to row58 and it is already visually slow.

It needs to work of every 3rd row.

All help much appreciated.

 

[RoryA]

Hi,

On the assumption that you are doing every third row for a set number of rows, something like this:

Sub Update_Actuals()
'
' Update_Actuals Macro
' Updates Actuals on Resources Data worksheet
'


'

Dim lRow as Long

Application.screenupdating = False
For lRow = 7 to 757 Step 3

    With Range(Cells(lRow, "S"), Cells(lRow, "KF"))
        .FormulaR1C1 = Cells(lRow, "R").FormulaR1C1
        .Value2 = .Value2
    End With

Next lRow
Application.screenupdating = True
End Sub

 

[Puertorekinsam]

Sub Update_Actuals()
'
' Update_Actuals Macro
' Updates Actuals on Resources Data worksheet
'


'
Application.ScreenUpdating = False
For i = 7 To 2257 Step 3

Selector = "R" & i
Paster = "S" & i & "KF" & i
    Range(Selector).Copy
    Range(Paster).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
Next
   

Application.ScreenUpdating = True


End Sub

 

[Puertorekinsam]

I think Rory's code might be a bit more efficient than mine,

but be sure to put the following lines before and after the loop code for faster results:

application.screenupdating=false

and

application.screenupdating=true

 

[SaraB802]

Hi,

On the assumption that you are doing every third row for a set number of rows, something like this:

Sub Update_Actuals()
'
' Update_Actuals Macro
' Updates Actuals on Resources Data worksheet
'


'

Dim lRow as Long

Application.screenupdating = False
For lRow = 7 to 757 Step 3

    With Range(Cells(lRow, "S"), Cells(lRow, "KF")
        .FormulaR1C1 = Cells(lRow, "R").FormulaR1C1
        .Value2 = .Value2
    End With

Next lRow
Application.screenupdating = True
End Sub

Hi Rory

Tanks for this I am getting a syntax error on the row

With Range(Cells(lRow, "S"), Cells(lRow, "KF")

Any idea why?

 

[RoryA]

Apologies - I missed a closing parenthesis. I have adjusted the code above.

 

[SaraB802]

Hi Rory,

That fixed it great, now it is coming up with application defined or object defined error on the above phrase.

.Value2 = .Value2

Thanks

 

[RoryA]

Do you have any merged cells?

 

[SaraB802]

No merged cells.

 

[RoryA]

Can you upload a file for review somewhere (eg OneDrive / Dropbox)? I'm struggling to think of what else might be the problem.