Copy/Paste of Range

Question

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?

• Clusks: See VBA best practices, particularly the part about avoiding the use of select/activate.
• user3598756: explain in better detail what's your goal: what do you want to copy from and paste to? Are you interested in values only?
• Noodles: 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.
• Nathan_Sav: Use range(dest).value=range(source).value when you can

Answer 1

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