Copy a range of data to new worksheet when matching data found

I hope somebody can help me as I am new to macro and do not have sufficient knowledge to fix my problem.

I would like to create a workbook for my students' personal information. I am looking for macro to copy rows based on partial cell content of a column. I would like to copy those data to other new worksheets when a certain criteria met.

Below is my sample:

(Sheet: Student Info)
Name: Peter
Staff ID: E90001
Class: 4D
---------
Name: Mary
Staff ID: E90103
Class: 2E
---------
Name: John
Staff ID: E93125
Clasee: 3A

I would like the macro to copy the information of each student to a new worksheet. For example, when I run the macro from row 1 and the cell contain the word "Name:", all information about this student will be copy to a new worksheet (the worksheet name base on the student name). The result like this one:

(Sheet: Student Info)
Name: Peter
Staff ID: E90001
Class: 4D
---------
Name: Mary
Staff ID: E90103
Class: 2E
---------
Name: John
Staff ID: E93125
Clasee: 3A

(Sheet: Peter)
Name: Peter
Staff ID: E90001
Class: 4D

(Sheet: Mary)
Name: Mary
Staff ID: E90103
Class: 2E

(Sheet: John)
Name: John
Staff ID: E93125
Clasee: 3A

Can anyone help?

Vishesh's picture

For solution check this

More details

Hi Vishesh,

Thank you for your kindly help :) This macro help me a lot.

The sample in my previous post is not good enough. Actually the item for each records are different.

For example:

(Sheet: Student Info)
Name: Peter
Staff ID: E90001
Class: 4D
Achievements : Singing contest winner
Outstanding Student 09-10

---------
Name: Mary
Staff ID: E90103
Class: 2E

---------
Name: John
Staff ID: E93125
Clasee: 3A

---------

Is it possible to use line break as indicator to copy another student to a new worksheet?

Thank your for yor help

Vishesh's picture

For the code to run

For the code to run successfully there should be a blank row after every student record.