1
\$\begingroup\$

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?

\$\endgroup\$
  • \$\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
1
\$\begingroup\$

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 | |
\$\endgroup\$

Your Answer

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