Solved

copy rows that contain a specific word

Posted on 2010-11-26
5
242 Views
Last Modified: 2012-05-10
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
Comment
Question by:kwatt562
  • 3
5 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34220229
Do you have a sample file?

Cheers

Dave
0
 

Author Comment

by:kwatt562
ID: 34220250
0
 

Author Comment

by:kwatt562
ID: 34220253
Hi Dave, file, attached.
Thanks a lot
0
 
LVL 3

Accepted Solution

by:
byronwall earned 500 total points
ID: 34220805
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
 

Author Closing Comment

by:kwatt562
ID: 34222620
Genius! works perfectly
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now