Would like a more efficient way to Copy/PasteSpecial

  • Thread starter Thread starter StevenM
  • Start date Start date
S

StevenM

I was doing this:

Range("H1:H5").Select
Selection.Copy
Range("B1:B5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

and then came across this technique which apparently
is more efficient:

Range("H1:H5").Copy Range("B1:B5")



Can you do the same thing when trying to PasteSpecial Values?
Is there a more efficient way to do this:
Range("H1:H5").Select
Selection.Copy
Range("B1:B5").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


Can you do something like?
Range("H1:H5").PasteSpecialPaste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False Range("B1:B5")
 
I was doing this:

    Range("H1:H5").Select
    Selection.Copy
    Range("B1:B5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

and then came across this technique which apparently
is more efficient:

    Range("H1:H5").Copy Range("B1:B5")

Can you do the same thing when trying to PasteSpecial Values?
Is there a more efficient way to do this:
    Range("H1:H5").Select
    Selection.Copy
    Range("B1:B5").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Can you do something like?
    Range("H1:H5").PasteSpecialPaste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False   Range("B1:B5")
Click to expand...

Not tested, but I think it should be two lines like this

Range("H1:H5").copy
Range("B1:B5").PasteSpecial
 
Hello:

PY And Associates had a good answer. You can even use the Cells format by
pointing to the first cell of the target range for the paste:

Public Sub Tester()
Range("H1:H5").Copy

Range("A1:A5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


**** Or ******
Cells(2, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub
 
Couldn't you also specify the destination on the copy line, like so:

Range("H1:H5").copy Destination:=Range("B1:B5").PasteSpecial
 
Back
Top