Macro input Value and extract rows accordingly

Question

This is an excel sheet with 4000 rows * 200 columns.

one column has 10 different names in the column.

Function of my macro is to first ask for the name of person whose report is required. Suppose I say "Ram" it will extract all the rows equal to that name and save the values in new workbook and save the file name as "Ram"

Answer 1

This is how I would approach the macro:

' Prompt the user for a name '
Name = PromptForName

' Get your sheets (and create a new workbook) '
Set Src = ActiveSheet
Set Book2 = Workbooks.Add
Set Dst = Book2.Sheets(1)

' Copy headers from row 1 '
Src.Rows(1).Copy
Dst.Rows(1).PasteSpecial

' Assuming column A is your name column '
For Each cell In Src.Range("A2:A" & Src.UsedRange.Rows.Count).Cells
    If cell.Value = Name Then
     Src.Rows(cell.Row).Copy
     Dst.Rows(2).Insert Shift:=xlShiftDown
    End If
Next

' Specify path to save (with name) '
Book2.SaveAs Name & ".xls"