Speeding up VBA involving cut, copy, paste

[zakynthos]

I've heard that by avoiding the use of the clipboard, you can speed up the running of VBA.

Is there anyway the following code could be amended so that the clipboard is not used, to give me faster running time:

Range("a6:b8").Select
Selection.Cut
Range("a3").Select
ActiveSheet.Paste
Range("a1:b5").Select
Range("a1").Select
ActiveCell.FormulaR1C1 = "Inspection 2. Outcome"
With ActiveCell.Characters(Start:=1, Length:=21).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("a1:b5").Select
Selection.copy
Range("A13").Select
ActiveCell.FormulaR1C1 = "=VALUE(R[-9]C[1])"
Range("A14").Select
'text to col
Range("A21").Select
Selection.copy
Range("A22").Select
SendKeys "{ENTER}"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
Selection.TextToColumns Destination:=Range("A22"), DataType:=xlFixedWidth, _
OtherChar:=".", FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(13, 1)), _
TrailingMinusNumbers:=True
Application.ScreenUpdating = True

_______________________________________

With thanks

 

[njimack]

You don't need to select cells to perform actions on them. Something like this (untested)

Range("a6:b8").Cut Destination:=Range("a3")

With Range("a1")
    .Value = "Inspection 2. Outcome"
    With .Characters(Start:=1, Length:=21).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End With
    
Range("A13").FormulaR1C1 = "=VALUE(R[-9]C[1])"

'text to col
Range("A21").Copy
Range("A22").PasteSpecial xlValues

ActiveWorkbook.Save
Range("A22").TextToColumns Destination:=Range("A22"), DataType:=xlFixedWidth, _
OtherChar:=".", FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(13, 1)), _
TrailingMinusNumbers:=True
Application.ScreenUpdating = True

 

[JamesW]

Hi,

1. Don't use .Select, instead use things like:

Range("A6:B8").Cut Destination:= Range("A3")

Range("a1").Value = "Inspection 2. Outcome"

2. You have Application.ScreenUpdating = True at the end, but where do you set it to false?

 

[klarowe]

Instead of using .select, change it to just:

    Range("Range to Cut").Cut ([Destination Cell/Range])
    Range("Range to Copy").Copy ([Desination Cell/Range])

Edit: Yea, what they said.. lol

 

[zakynthos]

Thanks so much, it works fine - I'll use this method in future