search and copy columns to clipboard

[tomleitch]

Hi all,

I have some code that I have butchered to try and find values in a column on my spreadsheet.

Basically what I am trying to do is that and cell in column H that has a matching date, I want to copy some columns from that row - to end up pasting them into a new sheet.

At the minute it highlights the matching cells yellow.... but instead of that I want it to copy cells from columns A, B and H from that row and have all the combined info in the clipboard so that I can paste into an email body....

Can anyone help?

Many thanks - Tom

Dim fnd As Date, FirstFound As String
Dim FoundCell As Range, rng5 As Range
Dim myRange As Range, LastCell As Range
'What value do you want to find (must be in string form)?
fnd = Sheets("SETTINGS").Range("A14").Value
MsgBox fnd
Set myRange = Sheets("OPS PLANNER").Range("H10:H5010")
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)
'Test to see if anything was found
If Not FoundCell Is Nothing Then FirstFound = FoundCell.Address Else GoTo NothingFound

Set rng5 = FoundCell

Do Until FoundCell Is Nothing
Set FoundCell = myRange.FindNext(after:=FoundCell)
Set rng5 = Union(rng5, FoundCell)
If FoundCell.Address = FirstFound Then Exit Do
Loop


'Highlight Found cells yellow
Interior.Color = RGB(255, 255, 0)
GoTo 100


'Error Handler
NothingFound:
MsgBox "No values were found in this worksheet"