Splits Data base on Column Criteria

[markverdad]

Hi to All,

Need to separate a Sheet into multiple Sheets base on Column criteria.
I have created a code via macro recording, i don't know how shorten my code.
Since i have to separate the sheet in a specified column. If i have 35 criteria i just copy the the record code 35 times (troublesome).

Here have attached the code i did.

Please somebody help me for this.
Final Schenker SOA Germany for waste.txt

[jaslake]

Hi Mark

Welcome to the Forum!!

The Code can be rewritten without the need to do this

If i have 35 criteria i just copy the the record code 35 times (troublesome).

But to do so without Data against which to test it would be fruitless.

What I'd suggest you do is post a Sample File of your Data. Include a Worksheet in that Sample File called "Lists". In Worksheet "Lists" Create a separate Column for each of these Criteria. Also have a Column that lists all the Worksheet Names you wish to create.

The Code will use these "Lists" to do your Worksheet Creation and as Filter Criteria. We'll need to know which Criteria goes with whch Worksheet.

[markverdad]

Hi Jaslake,

Here's the files.
The original file and the file after macro is done.

[jaslake]

Hi Mark

It appears you're splitting the File based on Customer Number (Column 14). It also appears that all Sheets except two have Data for a Single Customer Number.

The two exceptions appear to be Düsseldorf Diverse and Kontoauszug _ EUR.

The Code you provided would indicate you're placing a Customer Name and Address in Cells A19, A20 and A21 based on Customer Number in Cell K36.

Splitting the File based on Customer Number is rather straight forward. We'll need to know the exceptions (for example...Düsseldorf Diverse and Kontoauszug _ EUR) and any others that exist. Please provide a list of those exceptions in Vertical Format.

To place the Names and Addresses in the Created Worksheets we'll need a list of those Names and Addresses by Customer Number in Horizontal Format.

[markverdad]

Hi Jaslake,

Thanks for checking my post .
Here's the Customer list file i'm using for splitting.

[jaslake]

Hi Mark

Are you suggesting that ALL of these go on one sheet

Array
DE48706602
DE02991
DE35513
DE25951
DE00096146
DE00122059
DE03039
DE35242
DE25852
DE00098
DE00014603
DE00020336
DE00146256
DE15805
DE00015336
DE33605

or that these go on one sheet
DE48706602
DE02991

and that these go on one sheet
DE35513
DE25951

and that these go on one sheet
DE00096146
DE00122059
DE03039

etc,etc,etc

[jaslake]

Hi Mark

I have this 99% resolved...have a couple of issues.

In the Schenker Customer list.xlsx there are items that have the same Sheet Name but appear to be not linked together, namely:

DE00137842 Kelsterbach SCHENKER DEUTSCHLAND AG LANGER KORNWEG 34E Kelsterbach 65451
DE21806 Kelsterbach SCHENKER DEUTSCHLAND AG LANGER KORNWEG 34E Kelsterbach 65451

and

DE17070 Munich SCHENKER DEUTSCHLAND AG CARGO TERMINAL MODUL A 4 Munich 85356
DE39652 Munich SCHENKER DEUTSCHLAND AG PAUL-HENRI-SPAAK-STR , TOR 21 8 Munich 81829

and

DE11754 Stuttgart SCHENKER DEUTSCHLAND AG LUFTFRACHTZENTRUM Stuttgart 70629
De00123576 Stuttgart SCHENKER (MESSE) DEUTSCHLAND AG MESSEPIAZZA Stuttgart 70629

Shoud they be? If not they will require DIFFERENT Sheet Names.

[markverdad]

Hi Jaslake,

Thanks!
With Sheetname for same City we can have:

Kelsterbach-DE00137842
Kelsterbach-DE21806

Munich-DE17070
Munich-DE39652

Stuttgart-DE11754
Stuttgart-DE00123576

[jaslake]

Hi Mark

Place these two Files in the same Folder. Workbook Schenker Customer list.xlsx can be open or closed, the Code will open it if required. With regard to Schenker Customer list.xlsx, please note the Grouping by Number. If you have additional Groupings change this line of Code

Please Login or Register  to view this content.

Run the Code from Schenker 28.11.2013 v3.xlsm...CTRL + x will fire the Code.

Let me know of issues...

Please Login or Register  to view this content.