| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement to copy an entire row to other worksheet w/ in same b
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement to copy an entire row to other worksheet w/ in same b
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement to copy an entire row to other worksheet w/ in sa
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. "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement to copy an entire row to other worksheet w/ in sa
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" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement to copy an entire row to other worksheet w/ in sa
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement to copy an entire row to other worksheet w/ in sa
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!! "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement to copy an entire row to other worksheet w/ in sa
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" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement to copy an entire row to other worksheet w/ in sa
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! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hshayh0rn" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I copy an entire worksheet to another with text & format? | Excel Discussion (Misc queries) | |||
| copy an entire row IF a statement is true | Excel Worksheet Functions | |||
| Is there a function to copy an entire worksheet? | Excel Worksheet Functions | |||
| If statement...copy values of cells on another worksheet. | Excel Discussion (Misc queries) | |||
| need a formula to copy entire row to next worksheet | Excel Worksheet Functions | |||
Hybrid Mode
