Populating a cell on another sheet triggered by a value in the cell on Original sheet

MaxxLevell

New Member
Joined
Nov 19, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Two or three weeks ago, I posted a question on here, and received answers from multiple helpful folks who helped me resolve my issue. As a result of that success, I now have a new issue that I've run into, and am hoping someone can direct me to an answer.

I originally requested help on copying a row from sheet1, to either sheet2 or sheet3, based on a 'Y' or 'N' in cell '19', and then deleting that row from sheet1. That code is here...

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Lastrow As Long, Lastrow2 As Long
    Dim ans As Long
 
    On Error GoTo myerror
 
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
 
    If Target.Column = 19 Then
        Application.EnableEvents = False
     
        Lastrow = Sheets("ICS Not Needed").Cells(Rows.Count, 1).End(xlUp).Row + 1
        Lastrow2 = Sheets("ICS Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
     
     
        ans = Target.Row
     
     
        If Target.Value = "N" Then
            Rows(ans).Copy Sheets("ICS Not Needed").Rows(Lastrow)
            Rows(ans).Delete
        End If
         
        If Target.Value = "Y" Then
            Rows(ans).Copy Sheets("ICS Completed").Rows(Lastrow2)
            Rows(ans).Delete
        End If
    End If
 
myerror:
    Application.EnableEvents = True
End Sub

My new issue is that I would like to show the date that the 'Y' or 'N' was entered in the same cell on the destination sheet the row is copied to...instead of the 'Y' or 'N'. Is this something that I can modify the original code to do, and, if so, can I get some help with how to go about that?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Lastrow As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 19 Then
  
      If Target.Value = "N" Then
         Lastrow = Sheets("ICS Not Needed").Cells(Rows.Count, 1).End(xlUp).Row + 1
         Target.EntireRow.Copy Sheets("ICS Not Needed").Range("A" & Lastrow)
         Sheets("ICS Not Needed").Range("S" & Lastrow) = Date
         Target.EntireRow.Delete
      ElseIf Target.Value = "Y" Then
         Lastrow = Sheets("ICS Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
         Target.EntireRow.Copy Sheets("ICS Completed").Range("A" & Lastrow)
         Sheets("ICS Completed").Range("S" & Lastrow) = Date
         Target.EntireRow.Delete
      End If
   End If
End Sub
 
Solution

MaxxLevell

New Member
Joined
Nov 19, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Lastrow As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 19 Then
 
      If Target.Value = "N" Then
         Lastrow = Sheets("ICS Not Needed").Cells(Rows.Count, 1).End(xlUp).Row + 1
         Target.EntireRow.Copy Sheets("ICS Not Needed").Range("A" & Lastrow)
         Sheets("ICS Not Needed").Range("S" & Lastrow) = Date
         Target.EntireRow.Delete
      ElseIf Target.Value = "Y" Then
         Lastrow = Sheets("ICS Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
         Target.EntireRow.Copy Sheets("ICS Completed").Range("A" & Lastrow)
         Sheets("ICS Completed").Range("S" & Lastrow) = Date
         Target.EntireRow.Delete
      End If
   End If
End Sub
Thank you very much...it works perfectly! I do have a question though...the Ranges are "A" and "S". I'm assuming that VBA just "knows" that "A" is the first cell in the row, and "S" is the last cell in my row. Is that correct? I'm hoping to start understanding this better so I don't have to come on here so often looking for help lol.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
A & S are both column letters so it's pasting the values starting in column A & changing the value in column S to the date.
 

MaxxLevell

New Member
Joined
Nov 19, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
A & S are both column letters so it's pasting the values starting in column A & changing the value in column S to the date.
Thank you for the clarification! I appreciate your help on this.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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