How to make my code more efficient

[azizrasul]

I get told on this forum, and quite rightly so, that I should avoid a liberal use of Select, which is what the macro recorder tends to produce. However when I try

With Sheets("DETAILED - KPI 5")
    With .Range("C130:C" & Sheets("Sheet2").UsedRange.Rows.Count - 3 + 130)
        .Cut
    End With
    With .Range("I130")
        .Paste 'Object doesn't support this property or method.
    End With
End With

it doesn't work, but when I try

With Sheets("DETAILED - KPI 5")
    With .Range("C130:C" & Sheets("Sheet2").UsedRange.Rows.Count - 3 + 130)
        .Cut
    End With
End With

Sheets("DETAILED - KPI 5").Select
Sheets("DETAILED - KPI 5").Range("I130").Select
Sheets("DETAILED - KPI 5").Paste

it does?

 

[tigeravatar]

azizrasul,

Give this a try:

With Sheets("DETAILED - KPI 5")
    .Range("C130:C" & Sheets("Sheet2").UsedRange.Rows.Count - 3 + 130).Cut .[I130]
End With

Hope that helps,
~tigeravatar

 

[azizrasul]

Yes that did it. I would never have been able to work that one out. Many thanks.