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?

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