Move an entire row from one sheet to another sheet only when 2 different cell conditions are true.

PCaffrey

New Member
Joined
Apr 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I have 3 worksheets in the workbook called Shop_Turnover, Cmpltd and WeekendLECs.
The Shop_Turnover has a dropdown in Columns K and M that contains Cmpltd.
I want the below items to be macros.
I want to move the entire row to the Cmpltd worksheet only after by Columns K and M contain the Cmpltd entry.
I also want to copy these same rows to the WeekendLECs worksheet.
I have some code that works for moving the entire row based on Column K but adding the Column M criteria has me stumped.
SHOP_TURNOVER worksheet code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Z As Long
    Dim xVal As String
    On Error Resume Next
    If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Z = 1 To Target.Count
        If Target(Z).Value > 0 Then
            Call MoveBasedOnValue
        End If
    Next
    Application.EnableEvents = True
End Sub

[B]Module 1 code:[/B]
Sub MoveBasedOnValue()

    Dim xRg As Range
    Dim xCell As Range
    Dim A As Long
    Dim B As Long
    Dim C As Long
    A = Worksheets("SHOP_TURNOVER").UsedRange.Rows.Count
    B = Worksheets("WeekendLECs").UsedRange.Rows.Count
    If B = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("WeekendLECs").UsedRange) = 0 Then B = 0
    End If

    Set xRg = Worksheets("SHOP_TURNOVER").Range("K1:K" & A)
    On Error Resume Next
    Application.ScreenUpdating = False
    For C = 1 To xRg.Count
        If CStr(xRg(C).Value) = "Cmpltd" Then
            xRg(C).EntireRow.Copy Destination:=Worksheets("WeekendLECs").Range("A" & B + 1)
            xRg(C).EntireRow.Delete
            If CStr(xRg(C).Value) = "Cmpltd" Then
                C = C - 1
            End If
            B = B + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Just to confirm, you do not want to move the row until BOTH columns K and M in a row contain "Cmpltd".
Is that correct?
Just want to make sure it is an "AND" situation, and not an "EITHER OR" situation, as that will affect how the code needs to be updated.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
Also, how are columns K and M being updated (manually or by formula or link)?
And you you only need to check the row that was just updated, right?
 

PCaffrey

New Member
Joined
Apr 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
K and M are manually updated and yes only need to check the row that was just updated.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,928
Messages
5,639,043
Members
417,066
Latest member
rhenman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top