VBA to copy and post data

[Brandon10131]

Hello,

I am trying to post copy and paste data rows from one sheet labelled PMs to another labelled Data Summary. I am using a column value of "Incomplete" in column G. I have done it somewhat successfully with help from this forum. I however am having a problem with data not being transferred if there is already another incomplete listed in a row below one which I update. Example being row 23 has an incomplete then row 18 needs it. The row 18 will not transfer over without me removing the 23 incomplete and going in a sequential order.

I also did a Row +5 to ensure it doesn't interfere with other info on my first sheet listed as Data Summary as I couldn't figure out how to ensure it went to a non filled row. Any help or nudge in the right direction would be appreciated greatly. Thank you!

If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("PMs").Cells(Rows.Count, "G").End(xlUp).Row + 5

If Target.Value = "Incomplete" Then
Rows(Target.Row).Copy Destination:=Sheets("Data Summary").Rows(Lastrow)
End If
End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("G:G")) Is Nothing Then
      If LCase(Target.Value) = "incomplete" Then
         Target.EntireRow.Copy Sheets("Data Summary").Range("G" & Rows.Count).End(xlUp).Offset(1, -6)
      End If
   End If
End Sub

 

[Brandon10131]

Thank you that worked like a charm!

I will admit I am confused as to how you managed to tell Excel to select the PMs sheet without mentioning it in the actual code?

 

[Fluff]

With event code there is no need, as it's in the sheet code module it works on that sheet.

 

[Brandon10131]

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.