Add data from one sheet to another if value is met

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Add data from one sheet to another if value is met

Post by 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.

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by 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 ...?
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by ABabeNChrist »

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

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by 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.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by ABabeNChrist »

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

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by ABabeNChrist »

Hi HansV
I think I got part of it figured out, A small part :grin:
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 :clapping:

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by 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?
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by 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)

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by HansV »

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

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: Add data from one sheet to another if value is met

Post by sdckapr »

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

Steve

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by HansV »

I'd prefer to use

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

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by 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
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by 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

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by 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
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by 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.

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by 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)
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by ABabeNChrist »

HansV
You have been so very helpful and I am VERY THANKFUL
:clapping: :clapping: :clapping: :clapping: :clapping:
Thank You Once Again

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by 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

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add data from one sheet to another if value is met

Post by 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.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Add data from one sheet to another if value is met

Post by 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