If Cell = X Copy Entire row to another sheet

Hi, I looked at few threads similar to this, but I couldn't find what I was looking for. So I need some help writing a macro.

In my spreadsheet I need to find the following:- In the Data Sheet, if the word "Apple" is detected I want the entire row to be copied to the Apples Sheet. In the same macro if the word "Orange" is detected the entire row should be copied to the Oranges Sheet. The same if "Pears" is detected the entire row should be copied to the Pears Sheet

The headings don't need to be copied as they are already in place.

I hope someone can help me and I apologies if I overlooked something similar to this.

Thanks in advance!

Example.xlsx

if you can have sheets names = fruit names

Please Login or Register  to view this content.

if you can have sheets names = fruit names, check Pears cake

I did that but I get an error. Don't know what I'm doing wrong.

Yeah I fixed that, but I still get an error. For some reason I can't enter 2 words where I put the name of the sheet i.e. Apples Cake

Example.xlsm

Please Login or Register  to view this content.

variable without space

Ah Cheers, I got that example working now. But I'm having trouble trying to manipulate it for my database, as I'm not good at coding. I've attached an example with the exact fields. If you could help me out I'd be great full.

Example with copy of fields.xlsx

This will work. You need to save the workbook as xlsm or xlsb to save code in the workbook

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Cheers again, that works fine, but If I try to have more sheets and use the macro it says "Subscript out of Range" How can I adjust this? This is an updated spreadsheet.


Example with copy of fields.xlsm

be more heedful
if you can have sheets names = fruit names, check crips

Sorry, my bad oeldere, I was making it in a rush and I didn't check the spelling

Ok I see, I suppose I can change the tables around so it works. What about if I want to exclude Column P (Visibility) as I don't want to copy that to the other sheets, is there a way of doing that within the same macro?

it's very easy if you put this column as last column, so you can change
Range(Cells(arow, 4), Cells(arow, 16)).Copy Sheets(Apple).Cells(lastrow + 1, 4)
in
Range(Cells(arow, 4), Cells(arow, 15)).Copy Sheets(Apple).Cells(lastrow + 1, 4)