Hello,
I have the below code for moving an entire row to another sheet, it works fine but I just need a bit of help changing something if possible.
it currently moves the rows to the new sheet but it it also copies the formulas and is causing to return errors, #VALUE!.
is there a way that maybe when the data gets copied to the new sheet it pastes it as special values only to removed the formulas but retain the data?
please help me.
I have the below code for moving an entire row to another sheet, it works fine but I just need a bit of help changing something if possible.
it currently moves the rows to the new sheet but it it also copies the formulas and is causing to return errors, #VALUE!.
is there a way that maybe when the data gets copied to the new sheet it pastes it as special values only to removed the formulas but retain the data?
please help me.
VBA Code:
Sub MoveCellsSEA()
Dim xRg As Range
Dim xCell As Range
Dim A As Long
Dim B As Long
Dim C As Long
A = Worksheets("Files to Make Up (Sea) ").UsedRange.Rows.Count
B = Worksheets("Archive (Sea)").UsedRange.Rows.Count
If B = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Archive (Sea)").UsedRange) = 0 Then B = 0
End If
Set xRg = Worksheets("Files to Make Up (Sea) ").Range("A2:AG" & A)
On Error Resume Next
Application.ScreenUpdating = False
For C = 1 To xRg.Count
If CStr(xRg(C).Value) = "Completed" Then
xRg(C).EntireRow.COPY Destination:=Worksheets("Archive (Sea)").Range("A" & B + 1)
xRg(C).EntireRow.Delete
If CStr(xRg(C).Value) <> "" = "Completed" Then
C = C - 1
End If
B = B + 1
End If
Next
Application.ScreenUpdating = True
End Sub