Command Button To Move Data W/O Formulas

[usafmp37]

I have a complex situation here because there are a lot of moving parts in my workbook. I have sheet names Data, Active, Report, H and Completed.
I have a command button the moves my completed row to the Completed sheet and deletes the empty row in the Data sheet.
My problem is that the formulas are moving also and I am getting a few blank cells. I guess the formulas are still associated with the Data sheet and can't return any value since the data was moved. Is there a VBA to only move the data and not the formulas or a simple fix to my formulas to reference the data it moved with?

Here is one formula in the Data sheet that tells me which stage a project is at once a date has been added under the current stage.

=IFERROR(IF(INDEX(T_PROJ,ROW(T_PROJ[@BUILDER])-ROW(T_PROJ[[#Headers],[BUILDER]]),MATCH("STAGE "&N_ST&" DT",T_PROJ[#Headers],0))>0,"COMPLETED",INDEX(L_ST,IF(T_PROJ[@[STAGE 6 DT]]>0,6,IF(T_PROJ[@[STAGE 5 DT2]]>0,5,IF(T_PROJ[@[STAGE 4 DT]]>0,4,IF(T_PROJ[@[STAGE 3 DT2]]>0,3,IF(T_PROJ[@[STAGE 2 DT]]>0,2,IF(T_PROJ[@[AWARDED JOB]]>0,1,0)))))))),"")

Here is the VBA that moves my completed rows and deletes the empty ones.

Sub Button44_Click()
    Call MoveCompletedRows 'Macro1
    Call DeleteBlankRows 'Macro2
End Sub


Private Sub MoveCompletedRows()


Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
    Application.ScreenUpdating = False
        lastrow = Worksheets("Data").UsedRange.Rows.Count
            lastrow2 = Worksheets("Completed").UsedRange.Rows.Count
                If lastrow2 = 1 Then lastrow2 = 0
                    For r = lastrow To 2 Step -1
                        If Range("L" & r).Value = "COMPLETED" Then
                            Rows(r).Cut Destination:=Worksheets("Completed").Range("A" & lastrow2 + 1)
            lastrow2 = lastrow2 + 1
            Else:
        End If
    Next r
Application.ScreenUpdating = True
End Sub




Public Sub DeleteBlankRows()
    Dim SourceRange As Range
    Dim EntireRow As Range
 
    Set SourceRange = Application.Selection
 
    If Not (SourceRange Is Nothing) Then
        Application.ScreenUpdating = False
 
        For i = SourceRange.Rows.Count To 1 Step -1
            Set EntireRow = SourceRange.Cells(i, 1).EntireRow
            If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
                EntireRow.Delete
            End If
        Next
 
        Application.ScreenUpdating = True
    End If
End Sub

Thank you in advance.

 

[JoeMo]

With the objective of trying to minimize changes to your code, here's an untested modification to the "MoveCompletedRows" sub that I think will work to move data only. Modifications are shown in bold blue font.

Private Sub MoveCompletedRows()
Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
    Application.ScreenUpdating = False
        lastrow = Worksheets("Data").UsedRange.Rows.Count
            lastrow2 = Worksheets("Completed").UsedRange.Rows.Count
                If lastrow2 = 1 Then lastrow2 = 0
                    For r = lastrow To 2 Step -1
                        If Range("L" & r).Value = "COMPLETED" Then
                            With Rows(r)
                                .Copy
                                Worksheets("Completed").Range("A" & lastrow2 + 1).PasteSpecial Paste:=xlValues
                                .ClearContents
                            End With
            lastrow2 = lastrow2 + 1
            Else:
        End If
    Next r
Application.ScreenUpdating = True
End Sub

 

[usafmp37]

Thank you for the reply Joe. I tried the modification but it's still taking the formulas over.

 

[JoeMo]

Thank you for the reply Joe. I tried the modification but it's still taking the formulas over.

Have you stepped through the bit I modified to see what's happening. Don't see why it would paste anything other than values.

EDIT: In a quick test I get only values copied over. Did you try to re-type the modification? If so, did you notice that .cut became .copy? Try copying the modified portion directly from your browser and paste to the VBE.

 

[usafmp37]

Ah yes that worked! Thank you Joe I appreciate your help!

 

[JoeMo]

Ah yes that worked! Thank you Joe I appreciate your help!

You are welcome - thanks for the reply.
Always best to copy, from your browser, code provided by this site rather than trying to type it yourself. Saves us all time and effort.