# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  If Cell = X, Then Copy Entire Row Into A New Sheet

## McNulty

Hi - as per title, could someone advise me how to do this please? Basically, I have a report that I use everyweek and split up into various sheets according to the criteria.

So if any cell in column C contains "Apple", then I want it copied into a new s/s (with the headers). I basically have to do a number of iterations on this, can someone assist please? The main problem I invisage is asking Excel to copy the row into the new sheet underneath the last one.

Many thanks, Jimmy.

----------


## Special-K

You could try filtering on column C and manually copying and pasting the data into a new spreadsheet

----------


## McNulty

Right, that's what I'm doing now. But I'm trying to get the vba coding in, to avoid the manual work.

----------


## mewingkitty

Need to clarify something.
Is this a conditional behavior, ie. copy row to a new sheet IF certain criteria are met.

Or do you have several rows worth of information which you would like new sheets made, with the headers of each being named after that row's name.

----------


## McNulty

Conditional Behaviour. So firstly each sheet will carry the same set of headers. 

Then, if any cell in Column B contains "Apple", then the entire row needs to be copied into Sheet2 (one underneath each other after the header). If any cell in Column B contains "Orange", then the entire row needs to be copied into Sheet3, etc etc. I have a series of these I need to do.

Thanks for any help.

----------


## mewingkitty

An example workbook would be invaluable at this point.

I *think* that you want to go through all of column b, and for every cell that has a corresponding worksheet with the same name, add that information to the next empty row of that worksheet. If there is NOT a corresponding worksheet, do you want a new one made to suit, and continue on from there?

----------


## McNulty

Here you go, so basically Sheet 1 is a mock-up of my extracted data. And the resulting three tabs are what I'd like generated from the macro (the Sheets are always pre-defined - or at least will be when I write the vba code).

----------


## Militia

Here is an invaluable piece of code I frequently use. *I take no credit for its existence.* 

It doesn't do your job quite how you describe. Though it will split a database into sheets by column A. Either copy your column B to A, or change the code. So where it says A1, change to B1.

I'm sure you'll get a perfect solution shortly; knowledge is abundant around here.




```
Please Login or Register  to view this content.
```

----------


## mewingkitty

Okies right on, we're on the same page.

See attached file.

If the sheet name is going to have "my" at the beginning of it, you could modify the code to read from a mid of 2 to the end, that'd match it to everything after your "my". Currently it looks for exact text matches, and copies over the corresponding data. If no such sheet exists, it moves on.

----------


## McNulty

Hi - thanks for the help. The reason I wrote them as "MyOranges" is because the sheet name isn't going to be whatever the criteria of the cell is. They will take on pre-defined names which I can write into the VBA code pretty easily. 

Also, am I going mad, or is there no code attached to the file lol?

----------


## mewingkitty

```
Please Login or Register  to view this content.
```


You're going mad.
:D

When in design mode, double click on the command button,
or alternatively, just click on the "Visual Basic" button on the top left of the developer tab.

Let me know if you can work with that or if we need to go another direction.

----------


## mewingkitty

Sounds like something more along the lines of the attached file may be what you're after.

There are all kinds of ways to go about this. You could even have it pop up three boxes asking for the criteria for page one, then two, then three, and have it run the search from there.

There's still al lot I don't know about how you want this to work. Do you want to clear the three targets each time a search is run, or would you like it to check for duplicates..? At the moment if you keep clicking the button, it'll keep adding to the list.

----------


## mewingkitty

Or to eliminate the need to hop back into the code every time:

----------


## alfonse83

Hi,
I am trying to do something very similar. If you look at the attached spreadsheet you will see 6 tabs. Main, New Cust, Quote, etc.

"Main" will be the sheet that everything is inputted. If there is a "Y" under new customer, I want the entire row to copy to the tab "New Cust". If there is an "X" under quote, struct design, or graphic design I want it under its approp. tab. The Lisa tab should take certain Sales-Person and copy entire row on the Lisa tab. The certain Sales-Persons are BS, PK, PB, PD. 

Any help will be greatly appreciated.

----------


## mewingkitty

sry, haven't been on in a while.

Please explain when you want it to update. Could make it re-copy them all every time you changed which cell is active, but that's going to slow your sheet down a lot.

Personally I like buttons. I'd make it so that you check off/uncheck whatever you want and then have a button you press to run the update.

So, what's the plan.

----------


## Leith Ross

Hello MewingKitty,

I am closing this thread because Alfonso83 should have started a new thread and not posted a related problem in another member's thread. If you want to follow up on this, I suggested you send Alfonso83 a private message or visitor's message and go from there. Not blaming you for this, we (the mods) missed this one.

----------

