Copy Row Based On Cell Value To Another Worksheet

  • Hello trying to do something that is probably pretty simple, but I have no experience at all with Marcos.

    In cell F I have either 1's or blanks.

    If cell F has a 1 i would like it to copy over the rows A-J to a new work sheet.

    If in cell F the 1 is changed back to a blank, it would be removed from the said work sheet.

    Doesn't have to be Automatic, I can create a but to run the Macro.

    From what I have been reading this is "Dynamic" but i cant find anything that provides a similar code for me to tune to fit my needs.

    ANY HELP is much appreciated.

    Thank you, thank you, thank you in advance!

  • royUK

    Changed the title of the thread from “Newbee = Copy Row Based On Cell Value To Another Worksheet” to “Copy Row Based On Cell Value To Another Worksheet”.
  • Quote

    If in cell F the 1 is changed back to a blank, it would be removed from the said work sheet.

    How would Excel know what rows to remove if they have been removed from the original sheet to a new one?

  • Not to remove, just to copy over. Removed was a bad word choice. I guess update is better.

    I used this code... but it doesn't update when I delete "1" on the master sheet.

    1. Sub CopyYes() Dim c As Range Dim j As Integer Dim Source As Worksheet Dim Target As Worksheet
    2. ' Change worksheet designations as needed Set Source = ActiveWorkbook.Worksheets("Sheet1") Set Target = ActiveWorkbook.Worksheets("Sheet2")
    3. J = 1 ' Start copying to row 1 in target sheet For Each c In Source.Range("E1:E1000") ' Do 1000 rows If c = "yes" Then Source.Rows(c.Row).Copy Target.Rows(j) j = j + 1 End If Next c
    4. End Sub

  • Of course.

    It is a workbook of songs for a live band.

    Sheet 1 is a Master list containing song name, artist, key signature etc.

    One the columns is labeled current set list. If a 1 is marked in this column the row that the "1" is marked in will be copied and placed in Sheet 2 "Current Set"

    The code that I listed above worked except that Sheet 2 just does not update if "1" is deleted from the column in Sheet 1 master list. Or to phrase differently, if a song is chosen not to be a part of the the current set list, and the "1" is deleted on the master list. Sheet 2 does not reflect this change

    I am attaching a picture as well. Thank you for your help! Im sorry if I am not explaining it well.

  • Replace the code with this and the problem should be resolved.