# Copy/Paste of Range

I have gone through all steps to optimize the code, deactivating screenupdate, deactivating calculation, events, and pagebreaks removed the unnecessary selects, adding constants, and etc. However, I don't think my algorithm for coping is the best one, even if it is probably the simplest of them all.

For Each cell In currentRange
If cell = vbNullString Then Exit For
cell.Select
cellValue = cell.Value
If cellValue = "x" Then
Sheets(sourceData).Activate
'ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1").Select
ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1").Copy
Sheets(pasteSheet).Activate
Rows(destinationCell).Select
Set destinationRange = ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1")
'destinationRange.Select
destinationRange.PasteSpecial xlPasteValues
Rows(destinationCell).Range("H1").Cut
Rows(destinationCell).Range("G1").Insert
'ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1").Select
destinationCell = destinationCell + 1
Worksheets(sourceData).Select
End If
Next


I copy a range from a row into another sheet, while shifting values from H to G column. I did not manage to create this functionality with .Copy Destination:=, which I think is faster.

What would be the best way to optimizing this code?

• See VBA best practices, particularly the part about avoiding the use of select/activate. – Clusks Dec 8 '16 at 8:55
• explain in better detail what's your goal: what do you want to copy from and paste to? Are you interested in values only? – user3598756 Dec 8 '16 at 9:06
• See my answer here - stackoverflow.com/questions/29596432/…. Note programs should not use the clipboard. The clipboard is the user's tool not the programmers. – Noodles Dec 8 '16 at 9:28
• Use range(dest).value=range(source).value when you can – Nathan_Sav Dec 13 '16 at 10:58

## 1 Answer

Copying/pasting is never a good idea. Look at noodles link. You should use something like this....

Sheets("receivinigSheet").Range("A2:F2") = Sheets("sendingSheet").Range("A2:F2").Value