Add data from one sheet to another if value is met

[ABabeNChrist]

I have a Workbook with multiple worksheets. What I’m hoping to achieve is when a Safety hazard or Repair is noted on a worksheet, that those comment would be added to another separate worksheet. I do use a color coding to specify Safety hazard (Red fill) or Repair (Orange-yellow fill) and with comment in cell below.

[HansV]

How does the user note a safety hazard or repair? By changing the fill color of a cell, or by entering a value in a cell, or ...?

[ABabeNChrist]

Hi HansV
I use Data Validation as a dropdown and Conditional Formatting to fill cell when value is met.

[HansV]

Use the Worksheet_Change event of the worksheet to check whether the relevant cell has changed, and if so copy values to the other sheet.

[ABabeNChrist]

I believe I have an idea what you mean. I’ll fiddle around and see what I can do
Thank You HansV

[ABabeNChrist]

Hi HansV
I think I got part of it figured out, A small part
I attached a sample that I have been working on and here is part of the code I was able to come up with, I'm not sure how close I am, It may only be the first line, and you gave that one to me

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "A4" Then
If Range("A4").Value = "Bad, Ugly" Then
Sheet2.Range("A2").Value = "" 'If met the value from A6 will copt to Sheet2 A2
End If
If Range("A4").Value = "" Then
Sheet2.Range("A2").Value = ""
End If
End If
End Sub

Sample.xlsm

[HansV]

The line

If Range("A4").Value = "Bad, Ugly" Then

won´t do what you want. It should be

If Range("A4").Value = "Bad" Or Range("A4").Value = "Ugly" Then

But apart from that, I don´t understand what you´re doing. Could you explain as clearly and detailed as you can what exactly you want to accomplish?

[ABabeNChrist]

Hi HansV
I have a workbook with a sheet named Bathroom, on this sheet it is broken down into different areas of the Bathroom (Flooring, Walls, Tub, Plumbing, and so on), with each area having a dropdown using Data Validation. From there the user will choose from selection. If a selected word is chosen from that list then a comment pertaining to this area would be added to a summary page. Its mainly for when there is areas of concerned that need to be summarized. I will be doing this different areas (Sheets)

[HansV]

Sorry, you'll have to provide much more precise, detailed information if you want me to help you.

[sdckapr]

The line should be:
If Target.Address = "$A$4" Then

Steve

[HansV]

I'd prefer to use

If Not Intersect(Range("A4"), Target) Is Nothing Then

[ABabeNChrist]

Hi HansV
I not sure what to say ?
ummmm, If the word “Ugly or Bad” were selected in cell A4 of sheet1 then cell A6 of sheet1 would be copied and placed in cell A2 sheet2

[ABabeNChrist]

I got a little closer with this Code...

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Then
If Range("A4").Value = "Bad" Or Range("A4").Value = "Ugly" Then
Range("A6").Copy
Sheet2.Range("A2").PasteSpecial 'If met the value from A6 will copt to Sheet2 A2
Range("A1").Select
End If
If Range("A4").Value = "" Then
Sheet2.Range("A2").Value = ""
End If
End If
End Sub

[HansV]

Does this do what you want?

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A4"), Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("A4").Value
      Case "Bad", "Ugly"
        Sheet2.Range("A2").Value = Range("A6").Value
      Case Else
        Sheet2.Range("A2").Value = ""
    End Select
    Application.EnableEvents = True
  End If
End Sub

[ABabeNChrist]

Thank you HansV
Yes that works great
If I wanted to use this same code for another location on the same sheet, will this cause me problems or will I need to add to existing code, or do I do them separate. Also what type of code could I use that will place the value enter to sheet2 next available cell.

[HansV]

You can add If ... End If blocks for other cells:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A4"), Target) Is Nothing Then
    ...
  End If
  If Not Intersect(Range("A10"), Target) Is Nothing Then
    ...
  End If
  ...
End Sub

If you want to copy to the first blank cell in column A on Sheet2, replace Sheet2.Range("A2") with

Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp).Offset(1, 0)

[ABabeNChrist]

HansV
You have been so very helpful and I am VERY THANKFUL

Thank You Once Again

[ABabeNChrist]

Hi HansV
If I make the changes to
Sheet2, replace Sheet2.Range("A2")
with

Code: Select all

Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp).Offset(1, 0)

Do I not have to change

Code: Select all

      Case Else
        Sheet2.Range("A2").Value = ""

just in case know value is entered or selection was changed

[HansV]

You should omit the Case Else - if you add the value to the next available cell, there's no point in trying to clear it. You don't know which cell corresponds to the cell that has been modified anyway.

[ABabeNChrist]

I think I understand by using the End if in this fashion it does the same thing in that if the first value is not met it will go to the next and so on.

Code: Select all

If Not Intersect(Range("A4"), Target) Is Nothing Then
  End If
  If Not Intersect(Range("A10"), Target) Is Nothing Then