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    
    cellValue = cell.Value
    If cellValue = "x" Then                    
            'ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1").Select          
            ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1").Copy         
            Set destinationRange = ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1")           
            destinationRange.PasteSpecial xlPasteValues         
            'ActiveCell.Offset(0, -Selection.Column + 1).Range("F1:L1").Select          
            destinationCell = destinationCell + 1           
    End If

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?

  • \$\begingroup\$ See VBA best practices, particularly the part about avoiding the use of select/activate. \$\endgroup\$ – Clusks Dec 8 '16 at 8:55
  • \$\begingroup\$ explain in better detail what's your goal: what do you want to copy from and paste to? Are you interested in values only? \$\endgroup\$ – user3598756 Dec 8 '16 at 9:06
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Noodles Dec 8 '16 at 9:28
  • \$\begingroup\$ Use range(dest).value=range(source).value when you can \$\endgroup\$ – Nathan_Sav Dec 13 '16 at 10:58

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy