File Creation Automation

%3CLINGO-SUB%20id%3D%22lingo-sub-2324289%22%20slang%3D%22en-US%22%3EFile%20Creation%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324289%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20on%20a%20Windows%2010%20computer%20and%20Microsoft%20Excel%202008.%20I%20have%20a%20file%20with%201%2C000%20categories%20in%20column%20A.%20I%20would%20like%20to%20create%20a%20new%20Excel%20file%20for%20each%20of%20the%20categories%20in%20Column%20A%20and%20name%20the%20files%20after%20the%20category.%20Is%20there%20a%20way%20to%20automate%20that%20so%20I%20don't%20have%20to%20do%20it%20manually%3F%20Thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2324289%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2324491%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Creation%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1045230%22%20target%3D%22_blank%22%3E%40sethporter903%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20may%20be%20a%20way%20to%20do%20what%20you're%20asking%20via%20a%20macro%20or%20VBA%20routine.%20I%20don't%20use%20those%20much%20at%20all%2C%20so%20I'll%20let%20somebody%20else%20address%20that%20part%20of%20what%20you're%20asking.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20don't%20mind%20a%20different%20question%2C%20though%2C%20may%20I%20ask%20before%20you%20succeed%20in%20doing%20what%20you're%20asking%2C%20%22%3CU%3E%3CSTRONG%3EWHY%3F%3C%2FSTRONG%3E%3C%2FU%3E%22%20Why%20are%20you%20wanting%20to%20create%201%2C000%20different%20Excel%20files%2C%20one%20for%20each%20sub-category%20(or%20category)%20of%20whatever%20it%20is%20that%20clearly%20must%20have%20something%20in%20common%20in%20order%20to%20be%20in%20a%20single%20column%20to%20begin%20with%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%2C000%20separate%20sheets%20are%20going%20to%20be%20quite%20unwieldy.%20A%20single%20database%20can%20have%20thousands%20of%20rows%20and%20tens%20or%20hundreds%20of%20columns%2C%20and%20be%20very%20navigable%2C%20very%20useful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20have%20an%20entirely%20valid%20reason%2C%20a%20good%20purpose%20in%20mind...maybe%20the%201%2C000%20spreadsheets%20are%20going%20to%20be%20farmed%20out%20to%201%2C000%20different%20people%20to%20manage.%20But%20I'd%20sure%20want%20to%20know%20you're%20not%20just%20creating%20a%20major%20headache%20for%20yourself.%20And%20it's%20a%20question%20I'd%20ask%20if%20we%20were%20sitting%20down%20face-to-face%3B%20so%20I%20hope%20you%20don't%20mind%20my%20asking%20in%20this%20forum%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2327296%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Creation%20Automation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2327296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there%20thanks%20for%20asking.%20So%20essentially%20it%20has%20to%20do%20with%20billing%20purposes.%20The%20categories%20need%20to%20go%20to%20different%20organizations.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20should%20have%20mentioned%20there%20is%20some%20data%20within%20each%20category%20that%20will%20be%20need%20to%20sent%20over%20as%20well.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I am on a Windows 10 computer and Microsoft Excel 2008. I have a file with 1,000 categories in column A. I would like to create a new Excel file for each of the categories in Column A and name the files after the category. Is there a way to automate that so I don't have to do it manually? Thanks 

4 Replies

@sethporter903 

 

There may be a way to do what you're asking via a macro or VBA routine. I don't use those much at all, so I'll let somebody else address that part of what you're asking.

 

If you don't mind a different question, though, may I ask before you succeed in doing what you're asking, "WHY?" Why are you wanting to create 1,000 different Excel files, one for each sub-category (or category) of whatever it is that clearly must have something in common in order to be in a single column to begin with?

 

1,000 separate sheets are going to be quite unwieldy. A single database can have thousands of rows and tens or hundreds of columns, and be very navigable, very useful.

 

You may have an entirely valid reason, a good purpose in mind...maybe the 1,000 spreadsheets are going to be farmed out to 1,000 different people to manage. But I'd sure want to know you're not just creating a major headache for yourself. And it's a question I'd ask if we were sitting down face-to-face; so I hope you don't mind my asking in this forum,

@mathetes 

 

Hi there thanks for asking. So essentially it has to do with billing purposes. The categories need to go to different organizations. 

 

I should have mentioned there is some data within each category that will be need to sent over as well. 

Well, so far you've not convinced me that you need 1,000 separate spreadsheets to be created. You haven't described fully enough the big picture, but if it's a matter of billing 1,000 different clients (or some such) that too can be done from a single database, with itemization based on the distinctive "data within each category."

To be possibly very simplistic--and therefore possibly way off-base--I can even envision using MailMerge to create billing statements. But that's getting ahead and may or may not be the right move.

Is it in any way possible for you to post here a copy of (or a greatly reduced mockup of) your starting sheet, giving me (and others here with their unique perspectives) a first-hand look at the data you are talking about. And if you could supplement that with a more complete description of the bigger 'business process" into which your original question fits that too would help us all.
There is a better solution as opposed to creating 1000 files. Can you completely elaborate on the scenario please?