copy multiple row from one sheet to another depended on one cells text.

**bramer** · 01-30-2013, 03:47 AM

Hi, Hope life is great!

I am trying to impress my new employer, with my excel abilities. But then I ran into this problem.. using excel 2010 on german, I prefer English version, but its not possible on this workplace.

I have a main sheet, where you type in different information from each sale.
What I would like need to is excel to sort these out by product name.

Prod. no. info1 info2

hola 5 feat1 ohno
upla 4 feat2 ohyeah
Mila 2 feat3 super
upla 2 feat 2 ohmaybeyeah

I would like each row to copy itself into a sheet for each productname. notice that there will be more of the same productsname which have to go to same sheet.
is there anyway to do this?
Any help greatly appreciated.

**arlu1201** · 01-30-2013, 05:16 AM

So your product name is in column A?

If you run the macro once and the sheets get populated, tomorrow if you want to run the same macro, will you clearing up the main sheet and adding in information or would you be adding information to the next available row of the main sheet?

**bramer** · 01-30-2013, 05:38 AM

Thanks for your interest in my problem..

my product name is in column D.

the main sheet, will not be cleaned, I will add "sales" each day or week in the next row. So sheet one will have all the "sales" and then there will be products sheets for each of the products. But there will also come changes to the rows as the order processes.

I was also thinking if an IF function could do it. IF(D= product-name; then copy row; not copy) but could maybe do it for individual cells, but then the product lists will have spaces between the rows from not products..

I am quite new in the macro business, uses way to much IF functions

**arlu1201** · 01-30-2013, 05:52 AM

What do you mean by this?

but then the product lists will have spaces between the rows from not products..

Do you have a sample file that you could upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**bramer** · 01-30-2013, 06:15 AM

I mean that it will leave blank rows when the if function is not true..

anyway, I have attached a excel sheet. Thanks for all your help, hope we can manage to find something out.

**bramer** · 01-30-2013, 12:06 PM

ok, found a way to go around the problem..

But I am not really satisfied.

I made an IF function.

Formula:

Please Login or Register  to view this content.

Then filling this out the whole sheet, and it seems to work.

(I will recommend to have the product name listed in a product name sheet, so if you are changing names of products you only will have to do it one place)

to get around the empty rows in the product page then I run this from this post.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=512

But once this is run, it might be a nightmare to change back, so run in the end. (note to self)
But there must be another way..

to make this better.