• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

copy rows that contain a specific word

hi
I wonder if anyone can help with the following macro. In column B and are the persons firstname, columnc the last name, In column D is the attendance column, with a list box with the options YES/NO. What I want to do is when someone presses a button, and if yes appears next to the person first/lastname it copies these names to another sheet (sheet2) to B28 (firstname) and C28 (lastname) and B29/C29 and so on....is this possible?
0
kwatt562
Asked:
kwatt562
  • 3
1 Solution
 
Dave BrettCommented:
Do you have a sample file?

Cheers

Dave
0
 
kwatt562Author Commented:
0
 
kwatt562Author Commented:
Hi Dave, file, attached.
Thanks a lot
0
 
byronwallCommented:
Please see the attached spreadsheet for the full details.  The following code is executed at the button press:
Sub CopyYesNames()
    Dim start As Range
    Dim names_found As Integer
    
    Set start = Sheets("Sheet1").Range("D14")
    
    names_found = 0
    
    For Each cell In Range(start, start.End(xlDown))
        If cell = "YES" Then
            
            Sheets("Sheet2").Range("B28").Offset(names_found, 0) = cell.Offset(, -2)
            Sheets("Sheet2").Range("C28").Offset(names_found, 0) = cell.Offset(, -1)
        
            names_found = names_found + 1
        End If
    Next cell
End Sub

Open in new window


This assumes that the sheet names and cell positions will not change.  The code will adapt to as many names as desired.  It requires that the yes/no column not have any blanks between entries.  A yes answer must be indicated by YES (case sensitive).

Also, you mention splitting the first and last name, but the output sheet indicates name and ID number.  I have made the code follow the output sheet.  You could split the name into first and last and copy that instead.

Please follow up if this is not what you wanted.
 Book1.xlsm
0
 
kwatt562Author Commented:
Genius! works perfectly
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now