Solved Copy rows after cell contents changes

January 5, 2018 at 03:41:35
Specs: Windows 10
I am a novice when it comes to VBA and trying to work things out as I go. I'll give a summary explanation followed by a detailed one as I'm not sure what would work better for you.

Summary explanation:
I need a macro that triggers after a cell contents change that copies over entire rows from one worksheet, based on a criteria, and pasted to another, underneath the last value.

Detailed explanation:
I have a workbook with various worksheets but the relevant two are "RD activity review log" and "Paused Log". I need to keep track of how many times a line item in RD activity review log has a status (in column P) changed to "On hold" by using the Onhold Date in column R.

My idea was to run a macro called PausedLog each time a cell in the Onhold Date column in the "RD activity review log" worksheet is updated, for example a date was changed from 31/03/2018 to 30/05/2018 because it had been put on hold twice. A row should only be copied if there has been a change in the Onhold Date, i.e. null to something, or one date to a different date. My logic was that the PausedLog macro would copy over any rows where there is an "On hold" status in column P of the "RD activity review log" worksheet to the next available blank row above a named range in the "Paused Log" worksheet (just underneath a header row). The macro would then sort the Paused Log worksheet by the ID number column (column A).

The end result I wanted was that you'd have a list of items that had an "On hold" status on the Paused Log worksheet, so that if something had been put On hold more than once you'd see two or more rows with the same ID number but with ascending date values.

Here's the code I have so far. The problem I have is that it's copying everything so that I end up with exact duplicates, i.e. two rows with ID number 1 and On hold date of 31/03/2018 for example, rather than one line with a date of 31/03/2018 and one with 30/05/2018, because I don't know how to write the code to only copy rows with the changes, rather than all the rows with an "on hold" status.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheets("RD activity review log").Range("R:R")) Is Nothing Then PausedLog
End Sub

Sub PausedLog()

Dim c As Range
Dim RngDest As Range
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("RD activity review log")
Set Target = ActiveWorkbook.Worksheets("Paused Log")
Set RngDest = Worksheets("Paused Log").Range("RngDest")

For Each c In Source.Range("P1:P1000") ' Do 1000 rows
If c = "On hold" Then
Source.Rows(c.Row).Copy
RngDest.Insert Shift:=xlDown
End If
Next c

'Sort Paused worksheet by ID column in ascending order then by On hold date in ascending order
Range("PausedIDNumber").CurrentRegion.Sort _
key1:=Range("PausedSortCol"), order1:=xlAscending, Header:=xlYes
'Range("PausedIDNumber").CurrentRegion.Sort _
'key1:=Range("PausedSortDateCol"), order1:=xlAscending, Header:=xlYes

End Sub


Thank you very much for any help given.

message edited by ScottV


See More: Copy rows after cell contents changes

Reply ↓  Report •

✔ Best Answer
January 5, 2018 at 11:57:07
I hope you saw the updates to my post before you responded. The version that is there now is the complete version, including the comments about On Hold in Column P.

As far as the tags, when you click any tag, it adds a "start tag" and an "end tag" to your post. The text that you want to format goes between those tags. If you delete either tag, the system deletes the other.

(I had to add spaces to the example tags below to get them to show, but this is what I mean):

Start Tag:
< pre >

End Tag:

< /pre >

The "/" stops the formatting. e.g.

< b >If I remove the spaces in these tags, this line will be bolded< /b >

If I remove the spaces in these tags, this line will be bolded

Unfortunately, the pre tags prevent any nesting of other tags. You can nest bold and italic to get this, but you can't bold or italicize anything inside the pre tags. That's too bad, because there are times when I wish I could "highlight" a line in a macro by bolding it, but I can't.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
January 5, 2018 at 07:18:42
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
January 5, 2018 at 07:42:47
Here's the code I've got so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheets("RD activity review log").Range("R:R")) Is Nothing Then PausedLog
End Sub

Sub PausedLog()

Dim c As Range
Dim RngDest As Range
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("RD activity review log")
Set Target = ActiveWorkbook.Worksheets("Paused Log")
Set RngDest = Worksheets("Paused Log").Range("RngDest")

For Each c In Source.Range("P1:P1000") ' Do 1000 rows
If c = "On hold" Then
Source.Rows(c.Row).Copy
RngDest.Insert Shift:=xlDown
End If
Next c

'Sort Paused worksheet by ID column in ascending order then by On hold date in ascending order
Range("PausedIDNumber").CurrentRegion.Sort _
key1:=Range("PausedSortCol"), order1:=xlAscending, Header:=xlYes
'Range("PausedIDNumber").CurrentRegion.Sort _
'key1:=Range("PausedSortDateCol"), order1:=xlAscending, Header:=xlYes

End Sub


Reply ↓  Report •

#3
January 5, 2018 at 09:28:00
It does not appear that you followed the instructions in the How-To link. If you had, the code would look something like the code below. Note the retention of the indents and font format.

You need to enclose the code in the pre tags found above the Message input pane. Copy the code directly from the VBA editor and paste it between the pre tags, then preview your response to make sure it worked.

Option Explicit
Private Sub ColorNumbers()
Dim rw As Integer
  For rw = 1 To 56
   Cells(rw, 1).Formula = rw
   Cells(rw, 2).Interior.ColorIndex = rw
  Next
 MsgBox "Your List Of Color Index Values Is Complete"
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
January 5, 2018 at 09:48:54
Ok. Sorry for the inconvenience. I'll have another later or over the weekend. Thanks for your patience. Much appreciated.

Reply ↓  Report •

#5
January 5, 2018 at 10:11:52
In the meantime, why not consider using your Worksheet_Change macro to do all of the work and only copy/paste the Row that was changed?

e.g. expand on this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Column R and Column P contains On Hold
   If Target.Column = 18 Then
      If Cells(Target.Row, "P") = "On Hold" Then
        Target.EntireRow.Copy
'
' *** Add your Paste & Sort instructions here ***
'
      End If   
   End If
End Sub

BTW...Both your code and mine assumes that Column P already contains On Hold when the date is entered/changed in Column R. I based my suggestion on the fact that your code appears to make that assumption. If your users are entering/changing the date in Column R and then entering/choosing On Hold in Column P, neither of our codes are going to copy the data.


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
January 5, 2018 at 11:38:23
Thanks DerbyDad03. I'll have a play with your suggestion and let you know how I got on. I think I got confused with the pre tags because it seemed to put two sets of tags there and so I deleted one set.

Reply ↓  Report •

#7
January 5, 2018 at 11:57:07
✔ Best Answer
I hope you saw the updates to my post before you responded. The version that is there now is the complete version, including the comments about On Hold in Column P.

As far as the tags, when you click any tag, it adds a "start tag" and an "end tag" to your post. The text that you want to format goes between those tags. If you delete either tag, the system deletes the other.

(I had to add spaces to the example tags below to get them to show, but this is what I mean):

Start Tag:
< pre >

End Tag:

< /pre >

The "/" stops the formatting. e.g.

< b >If I remove the spaces in these tags, this line will be bolded< /b >

If I remove the spaces in these tags, this line will be bolded

Unfortunately, the pre tags prevent any nesting of other tags. You can nest bold and italic to get this, but you can't bold or italicize anything inside the pre tags. That's too bad, because there are times when I wish I could "highlight" a line in a macro by bolding it, but I can't.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#8
January 8, 2018 at 07:19:15
Thanks DerbyDad03. Your suggestion worked a treat. You're a true gent.The on hold bit is not an issue, but I'll point it out to my team so it's their fault if something doesn't track properly.

I've finally cracked it all! It's been painful but a great learning experience. Thank you so much for your help. Here's the code that I ended up with:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim PasteSht As Worksheet
    
    Set PasteSht = Worksheets("Paused Log")
    
    If Target.Column = 18 Then
      If Cells(Target.Row, "P") = "On hold" Then
        Target.EntireRow.Copy
        PasteSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
        Sheets("Paused Log").Activate
        With ActiveSheet.Sort
        .SortFields.Add Key:=Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=Range("R1"), Order:=xlAscending
        .SetRange Range("A1:V1000")
        .Header = xlYes
        .Apply
        End With
        

      End If
    End If

End Sub

message edited by ScottV


Reply ↓  Report •

#9
January 8, 2018 at 07:44:15
deleted what was here before as no longer relevant...

message edited by ScottV


Reply ↓  Report •

#10
January 15, 2018 at 07:19:06
Hi Again

Referring to your code from earlier

 If Target.Column = 18 Then
      If Cells(Target.Row, "P") = "On hold" Then
        Target.EntireRow.Copy
        PasteSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

How would I only copy the values in columns A, F and R, rather than the entire row? I can copy non-adjacent values from the same row by defining a range each time it updates, or copy any whole row using your code above. I just can't work out how to capture just certain column values from the updated row, rather than the entire row.


Reply ↓  Report •

Ask Question