vba code to remove a row of data from a list? find cut and paste?

[tonywatsonhelp]

Hi everybody,
I need a way to remove a row of data from a client list, and paste it into another sheet,

I have a list of clients in sheet1, the data starts in column A and goes all the way across to column FQ
in Sheet 2 I have a drop down box with all the clients names,
I need a way to cut and paste that clients data from my list into sheet 2 row 2 (row 1 will have headers) so the first cell to post into will be A2,
now some of the data is formulas so I need it to cut and paste values so as not to lose this data?

I've never used any kind of find and cut code before so I don't know where to start but if someone could help me with the vba code I need that would be amazing.

If I was writing the code in English it would go like this:

Look at name selected from dropdown box in cell C4, goto sheet1, look down column B and find matching client (there will only be one name to look for, no duplicates)
Select that clients entire row (or cells A to FQ if easier) cut, goto Sheet2 A2 paste values.

now when I look at that It doesn't seem that hard to do, but I can't get anything close so you help would be very much appreciated and is very needed.

Thanks

Tony

 

[wrightyrx7]

Whats happens to the data on Sheet 2 when you select a different name in C4?

Does the existing data get overwritten OR does the new data get pasted below the existing?

Also IF the data gets pasted below pretty soon your gonna paste data over cell C4 so not sure where your gonna select the name from after this.

 

[tonywatsonhelp]

Hi,
Thats a good question but I can deal with clearing the cell once I've finished with the data, this is a small part of a very large macro but basically every time it runs it will be pasting into sheet2 A2 which will not contain any data,
thanks for asking, I really hope someone can help as I can't write anymore of my macro until I've got this done?

thanks

Tony

 

[wrightyrx7]

Please make a COPY of your workbook to test this code. Let me know if I have understood and it does what you want.

Sub Find_Name()
    Dim FindString As String
    Dim Rng As Range
    
    FindString = Sheets("Sheet2").Range("B4")
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("B:B")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Rng.EntireRow.Copy
                Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlValues
                Rng.EntireRow.Delete
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

 

[tonywatsonhelp]

OMG, Thank you so much wrightyrx7, works perfect first try, I didn't have to change a thing,
brilliant, thank you so much,

Tony

 

[wrightyrx7]

No worries

Thanks for the feedback

 

[tonywatsonhelp]

Keep you eye out, I'm about to post part 2 (I think this is a bit easier but I'm still lost!)

Tony

 

[wrightyrx7]

Hahaha will do, but sure someone will beat me to it. There are lots of people on this forum taking the time to help people out. Lots more experience than me :P