Excel - Create Workbooks based on Column

[JackGat]

I have an excel file with 350 records. Each record has a specific quantity assigned to it. I would like
to create different spreadsheet based on the quanity listed in column L.

Sequence Company Contact Address City State Zip Phone Quantity Package
001 Company John Smith 10002 Street Citytown2 ST 10001 2125551001 QTY 2 P1523
002 Company John Smith 10003 Street Citytown3 ST 10002 2125551002 QTY 2 P1523
003 Company John Smith 10004 Street Citytown4 ST 10003 2125551003 QTY 2 P1523
004 Company John Smith 10005 Street Citytown5 ST 10004 2125551004 QTY 16 P1523
005 Company John Smith 10006 Street Citytown6 ST 10005 2125551005 QTY 18 P1523

This would mean that:

Records with a quantity of 2 would go into an excel file.
Records with a quantity of 16 would go into an excel file.
Records with a quantity of 18 would go into an excel file.

Right now, I do it manually and it takes quite a bit of time.

I am VBA light.


Thanks for any help you can provide.

 

[SkipVought]

Hi,

Is there a reason for a separate sheet for each quantity?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The reason I ask is that there may be other ways to VIEW the desired data on ONE sheet, like simply using the AutoFilter.

The purpose for dispursing the data into sheets is important to know and understand.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JackGat]

Hi! Thank you for the response.
We need to group like quantities into separate files as to reduce error.
Our production department takes the files based on quantity to fulfill orders.
Eventually each file is loaded into the mailing system we have.

It's old but it works okay.

 

[SkipVought]

So it is a transitional step, in a process.

There are several ways to do this:

Filter - COPY visible cells - select the associated sheet - Edit > Paste Special - VALUES.

or

From one of your qty sheets, use MS Query to GET the data as required.

Another question: Why would you have a column heading of Quantity AND QTY in the values for that column? THAT is downright dumb, as it destroys the numeric feature of the Quantity column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JackGat]

Thank you for the suggestion. In general the field where the quantity is placed is used for multiple reasons by different departments.

I can remove the QTY from the field to ensure it is a numeric field.

 

[SkipVought]

QTY is a MINOR issue!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

pivot tables might be worth looking at too...