Command Button To Move Data W/O Formulas

usafmp37

New Member
Joined
May 28, 2019
Messages
3
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.

Code:
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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.
Rich (BB code):
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

New Member
Joined
May 28, 2019
Messages
3
Thank you for the reply Joe. I tried the modification but it's still taking the formulas over.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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.
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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.
 
Last edited:

Forum statistics

Threads
1,089,437
Messages
5,408,214
Members
403,190
Latest member
RBrite

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top