Is there a macro to...

[lightbulb]

Is there a macro that would make it possible to lookup a certain name in a
column and anytime it finds that name it will copy a range of cells and paste
it into another worksheet? I.E. anytime the name Jim Smith is found in the
range A5:A200 it returns a range of cells next to it...if Jim Smith was found
in A5, it would copy cells C5:FN9 and paste them in a specified location on
another sheet

Right now I have it copying all colmns from the master sheet and if the name
is not assigned to that project it will return a 0, then I have a macro to
hide all rows that equal 0. Is there a better way to do this? Any hints on
the best way to do this?
Thanks!

 

[Tim879]

A quick way to do this would be to use autofilter.

select the row with the titles, enable autofilter, set the filter on
the name column to equal the value you want to return, then copy all
VISIBLE cells to a new worksheet

Here's a sample (assumes name is in column A)
Sub Macro1()
'
'
Dim cursheet As Worksheet
Dim searchstring As String

Set cursheet = ActiveSheet
searchstring = InputBox("Enter Name: ")
Rows("1:1").AutoFilter Field:=1, Criteria1:=searchstring

Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste

cursheet.Select
Selection.AutoFilter Field:=1
Application.CutCopyMode = False
Range("A1").Select


End Sub