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?
For solution check this
For solution check this url
http://excelexperts.com/solution-copy-data-range-new-worksheet
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
For the code to run
For the code to run successfully there should be a blank row after every student record.