If statement to copy an entire row to other worksheet w/ in same b

[hshayh0rn]

I would like to have an IF state that says something to the effect:

IF D4 = "Fail" then copy row4 to "Notes" (notes is another sheet in the
workbook)

This statement would repeat for every row on the sheet and needs to be real
time. AS I tab off of column D each time if "Fail" is the value in that cell
then the row should be copied. The first available row on the notes sheet is
row 3.

 

[Luke M]

Right click on Sheet tab, view code, paste the following in. Note that macro
checks whenever you make an edit (not simply tab over) the cell in column D.
Also, Fail is case-sensitive ("fail" will not trigger event).



Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Application.ScreenUpdating = False
StartSheet = ActiveSheet.Name
'Column D corresponds to 4
If Target.Column = 4 And Target.Value = "Fail" Then
Target.EntireRow.Copy
Sheets("Notes").Select
i = 3
FindBlank:
'Checks to find first blank row
If Worksheets("Notes").Range("D" & i).Value = "" Then
Worksheets("Notes").Range("A" & i).Select
ActiveSheet.Paste
Else: i = i + 1
GoTo FindBlank
End If
Application.CutCopyMode = False
Sheets(StartSheet).Select
End If
Application.ScreenUpdating = True

End Sub

 

[hshayh0rn]

That worked great. Would it be possible to also add a hyper link to the word
Fail on the page that links to column H of the row that was just copied?

For example:

Sheet 1, row 5 has the word "Fail" so row 5 is copied to the "Notes" tab.
I'd like to be able just click on any of the "Fail" entries and go right to
the notes column on the notes tab for that particular row.

 

[hshayh0rn]

Also, when I add a row to the sheet that has your code it breaks. I get a
debug error on the line where we're looking for the word "Fail".

 

[Luke M]

Hmm. I added an escape break in case an error occurs. Adds hyperlink to the
word "Fail" on current sheet, links to column H or copied data in the Notes
sheet.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer

On Error GoTo SubExit

Application.ScreenUpdating = False
StartSheet = ActiveSheet.Name
'Column D corresponds to 4
If Target.Column = 4 And Target.Value = "Fail" Then
Target.EntireRow.Copy
Sheets("Notes").Select
i = 3
FindBlank:
'Checks to find first blank row
If Worksheets("Notes").Range("D" & i).Value = "" Then
Worksheets("Notes").Range("A" & i).Select
ActiveSheet.Paste
Else: i = i + 1
GoTo FindBlank
End If
Application.CutCopyMode = False
Sheets(StartSheet).Select
Adden = "Notes!H" & i
ActiveSheet.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:= _
Adden, TextToDisplay:="Fail"
End If
SubExit:
Application.ScreenUpdating = True
End Sub

 

[hshayh0rn]

Now it works perfectly! No errors and the hyperlink works great. The text
size of "Fail" goes down to 7pt. If that controlable?

Thanks for your help!!

 

[hshayh0rn]

Alright Luke - I'm testing this a bit more and I'm going to be a little
greedy... Sheet 1 from where we are copying the rows from has some values in
column A. Basically my data looks similar to this:

A B C
Apples
Red sweet
Green sour
yellow tasty
Pears
Green yummy
yellow soft

So column A really begins a new group of tems for coulmn B and the rest of
the row. I would like when we copy the row to have the program look upwards
in column A and grab the first entry it finds and then add that entry to
column A on the "notes" sheet. Currently when we do the copy column A is
empty.

So then the copy would look like this on notes

A B C
Apples Red sweet
Apples Green sour
Pears Green yummy
Pears yellow soft

Also, (very greedy) I have the rows color coded and on the notes sheet I
added a column (at the end) that doesn't exist on Sheet 1 called notes.
Anyway, this column when we copy the row is uncolored. Could we determine the
color of the row we're copying and then fill in that color in column G for
the particular row?

Neither of these are curcial just would make the whole thing work and look
better.

 

[Luke M]

I'm afraid I don't know why the hyperlinks are defaulting to 7 pt font.
That's not your default font size by chance, is it?

While it may be possible to reference color/find previous line item, I'm
afraid that's beyond my knowledge/understanding. There are other's in these
forums who might know. I would recommend reposting this question (so that it
no longer appears as "answered", along with the link to this thread so they
know what's happened so far, in the Programming section of the Excel forum,
see if someone there can help.

Good luck in your endeavors!