CODEWITHSUNDEEP
vbaexcel
spittingfire
spittingfire

Reputation: 111

Copy and paste a range of cells from one sheet to another then clear data from orignal cells

I have the below code that works well, however what I will like to do is have the code modified to copy the data it will clear to Sheet2 for further investigating the continue to clear from the original sheet. All the code itself does is look at G and H. If H is smaller than G it then clears the contents of A:J. What I want now is to still clear the contents if the criteria is met however I want a copy of the cells copied to Sheet2 as well.

Sub ClearRange()

Dim myLastRow As Long
Dim i As Long

Application.ScreenUpdating = False

' Find last row
myLastRow = Cells(Rows.Count, "G").End(xlUp).Row

' Loop through range
For i = 5 To myLastRow
If Cells(i, "H").Value < Cells(i, "G").Value Then Range(Cells(i, "A"), Cells(i, "J")).ClearContents
Next i

Application.ScreenUpdating = True

End Sub

Thanks in advance for any assistance you can provide.

Upvotes: 0

Views: 679

Answers (1)

Jason Faulkner
Jason Faulkner

Reputation: 6558

You can just update this portion of your code:

' Loop through range
For i = 5 To myLastRow
    If Cells(i, "H").Value < Cells(i, "G").Value Then
        With Range(Cells(i, "A"), Cells(i, "J"))
            .Copy
            Sheets("Sheet2").Paste Destination:=Sheets("Sheet2").Range("A" & i)
            .ClearContents
        End With
    End If
Next

Upvotes: 1

Related Questions