Need Code to copy Data to other sheets on condition

Hello Guys,

I need to copy the data to other sheets on conditions
Below is example...

Source Data

Sheet1

CAT: 001
Percent
NA
NA
NA

DOG: 002
Percent
NA
15%
NA

Lion: 003
Percent
NA
NA
NA

Snake: 004
Percent
NA
NA
8.01%

Excel Sheet1 contains above data..
I am asked to Write macro to solve the Below purpose

1)Search "Percent" Column for values.. and return with its Animal Name & number on Sheet2..
Output would be -

DOG: 002
SNAKE: 004

As DOG: 003 & SNAKE: 004 have %value in it.


2) Split the Data on the basis of animal..
Sheet1 will have Cat: 001 Data
Sheet2 will have DOG: 002 Data
Sheet3 will have LION: 003 Data
Sheet4 will have SNAKE: 004 Data
and so
Note there might be n number of animal names in the sheet1


Please help

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

This will get you started,
It loops through the range.areas and copies and pastes them to a new sheet.

Please Login or Register  to view this content.

Maybe somebody else can work on the other part.

See the attached. Few lines added in Dave's code to take care of your Summary Sheet's requirement. Click the yellow button on Input Sheet to get the desired output on Summary Sheet and to add new sheets to have similar data as per Dave's code.

Parth! you should be very clear about the sheet layout before asking a question specially when you are requesting for a VBA code. You completely showed the data in a different way in your first sample workbook. Please take care of this in your future threads if any.

Anyways find the attached to see if this is what you are trying to achieve.

You're welcome Parth.
Please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

Good to know that Parth.
BTW you may also try this. See the attached sheet.