Move Changed Rows to another sheet

[JGOpsMgr]

Good morning, First let me say I am not a code writer. I am a simple operations manager and excel enthusiast. I use excel to track multiple functions within my operation. I have a sheet that is used by only one person. The data is in columns A-R. I would like to copy the entire row if something was changed in column K and paste it to another sheet. I am currently using another VBA I got online but it is cumbersome and takes up a lot of space to log every cell changed. I dont need to know who changed it nor do I need to know every cell that is changed. I simply want to copy and paste it if the one cell in column K with the header "Status" is changed. Below is the code I am using currently.

 

[HighAndWilder]

Good morning, First let me say I am not a code writer. I am a simple operations manager and excel enthusiast. I use excel to track multiple functions within my operation. I have a sheet that is used by only one person. The data is in columns A-R. I would like to copy the entire row if something was changed in column K and paste it to another sheet. I am currently using another VBA I got online but it is cumbersome and takes up a lot of space to log every cell changed. I dont need to know who changed it nor do I need to know every cell that is changed. I simply want to copy and paste it if the one cell in column K with the header "Status" is changed. Below is the code I am using currently.
View attachment 117982View attachment 117982

Welcome to Mr Excel.

Can I first ask why you are copying rows of data rather than looking at one set of data in different ways, maybe by using a filter?

Is the copied data going to be updated as well?

When the Status has changed unless the row is copied at that point, a record / log is kept of the change or the current Status can later be compared to a copy of the same data from a previous point in time then there is nothing to indicate that the row needs to be copied.

Does the copy need to take place as soon as the Status changes or can it be copied when you first next look at the data in the other sheet?

 

[mumps]

You have used a Workbook_Open macro so make sure that you place the macro in the code module for ThisWorkbook. Make sure that you have a sheet named "Differences" where the changed rows will be copied. You can use a different name if you wish. Just change the name in the code (in red) to suit your needs.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim v1 As Variant, v2 As Variant, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, i As Long
    Set WS1 = Sheets("AuditWS")
    Set WS2 = Sheets("SewingWS")
    Set desWS = Sheets("Differences")
    v1 = WS1.Range("K1", WS1.Range("K" & Rows.Count).End(xlUp)).Value
    v2 = WS2.Range("K1", WS2.Range("K" & Rows.Count).End(xlUp)).Value
    For i = LBound(v1) To UBound(v1)
        If v1(i, 1) <> v2(i, 1) Then
            With desWS
                WS1.Rows(i).EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[JGOpsMgr]

Welcome to Mr Excel.

Can I first ask why you are copying rows of data rather than looking at one set of data in different ways, maybe by using a filter?

Is the copied data going to be updated as well?

When the Status has changed unless the row is copied at that point, a record / log is kept of the change or the current Status can later be compared to a copy of the same data from a previous point in time then there is nothing to indicate that the row needs to be copied.

Does the copy need to take place as soon as the Status changes or can it be copied when you first next look at the data in the other

So just a brief explanation, I have multiple depts using similiar sheets that they enter the info for work orders in their respective depts.Each work order has multiple statuses before its complete. I have to update a master production each day with any changes in the status for each dept. I am very open to other options. I generally look at each log after the close of business each day.

 

[JGOpsMgr]

You have used a Workbook_Open macro so make sure that you place the macro in the code module for ThisWorkbook. Make sure that you have a sheet named "Differences" where the changed rows will be copied. You can use a different name if you wish. Just change the name in the code (in red) to suit your needs.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim v1 As Variant, v2 As Variant, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, i As Long
    Set WS1 = Sheets("AuditWS")
    Set WS2 = Sheets("SewingWS")
    Set desWS = Sheets("Differences")
    v1 = WS1.Range("K1", WS1.Range("K" & Rows.Count).End(xlUp)).Value
    v2 = WS2.Range("K1", WS2.Range("K" & Rows.Count).End(xlUp)).Value
    For i = LBound(v1) To UBound(v1)
        If v1(i, 1) <> v2(i, 1) Then
            With desWS
                WS1.Rows(i).EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Sorry Im not sure if I was clear on the need. I have a sheet that is called Audit that the changes are currently going to but it has way too much info and is hard to read. Would I still need the "differences" Sheet?

 

[HighAndWilder]

So just a brief explanation, I have multiple depts using similiar sheets that they enter the info for work orders in their respective depts.Each work order has multiple statuses before its complete. I have to update a master production each day with any changes in the status for each dept. I am very open to other options. I generally look at each log after the close of business each day.

If I was you I would set the sheet up so that when a change was made that the date and time of the change was logged in a separate column.. A red only summary can be produced on another sheet of just the columns that you need to see and only containing the rows where the status has changed during that day or any specified day.

Do you need to know what the status has changed from?

Is it likely that the Status will change more than once in a day?

 

[JGOpsMgr]

Ok so I created a column to timestamp the last change on the same sheet as you suggested. I really only need to see the changed status at the EoD so even though statuses may change multiple times it's only the last one i really need. What is the red only summary and how would i use it?

 

[HighAndWilder]

Ok so I created a column to timestamp the last change on the same sheet as you suggested. I really only need to see the changed status at the EoD so even though statuses may change multiple times it's only the last one i really need. What is the red only summary and how would i use it?

Put this code in the Worksheet Code Module.

Right click on the worksheet tab and select View Code.

It assumes that the date stamp is to go in column J but you can change that.

If column K changes the date is stamped.

Test it on a copy of your data.

In another sheet put this formula in Cell A1

=CHOOSECOLS(FILTER(Production!$A:$R,Production!$J:$J=TODAY(),"No Data"),{1,2,3,10,11})

Change 'Production' to the name of the sheet where your data is.

Change the list of numbers at the end to the column numbers of those that you want to be displayed.

It again, assumes that column J is where the data stamp is but you can change that letter in the formula.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.CountLarge > 1 Then
    Exit Sub
  End If

  If Not Intersect(Range("K:K"), Target) Is Nothing Then
  
    Range("J" & Target.Row).Value = Date
  
  End If

End Sub

 

[HighAndWilder]

Put this code in the Worksheet Code Module.

Right click on the worksheet tab and select View Code.

It assumes that the date stamp is to go in column J but you can change that.

If column K changes the date is stamped.

Test it on a copy of your data.

In another sheet put this formula in Cell A1

=CHOOSECOLS(FILTER(Production!$A:$R,Production!$J:$J=TODAY(),"No Data"),{1,2,3,10,11})

Change 'Production' to the name of the sheet where your data is.

Change the list of numbers at the end to the column numbers of those that you want to be displayed.

It again, assumes that column J is where the data stamp is but you can change that letter in the formula.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.CountLarge > 1 Then
    Exit Sub
  End If

  If Not Intersect(Range("K:K"), Target) Is Nothing Then
 
    Range("J" & Target.Row).Value = Date
 
  End If

End Sub

If this is what will be useful we can add in the column headers later.

 

[mumps]

Just change "Differences" in the code to "Audit".

it has way too much info and is hard to read

Do you want the code to clear all the old info in the Audit sheet before copying the new info?