Creating multiple workbooks from a list of names

[gibson2503]

I have two workbooks. The first workbook is simply a list of employee names in column A and employee ID numbers in column B, and only that one sheet in the work book. There are almost 1100 employees listed on it. The other workbook shows a work week in column A, hourly pay rate for that week in B, hours worked in C, and gross pay due in D. Employee name and ID number are in the top row of the sheet. There will be one sheet for each year, with each sheet having the same set up. So far I have 2012 and 2013. Before I start plugging data into that second workbook, I want to have one file saved with each file name being that of an employee. So, I will end up with almost 1100 files. Is there a way to use the list of 1100 names in the first workbook to save one copy of the second workbook for each employee name? Without clicking save as and typing in the employee name 1100 times?

[JBeaucaire]

Like so:

Please Login or Register  to view this content.

[gibson2503]

The suggestion from JBeaucaire worked out fairly well. The resulting files each had the name of the original template file, with the name of each employee added to it before the .xlsx . How would I make it where the resulting files only had the employee name? Like this SNUFFY,JOE.xlsx instead of ORIGINALSHUFFY,JOE.xlsx .

[JBeaucaire]

I'm looking at the code and can't figure out where the "Original" is getting added to the new filenames, it's not included in the new files, nor did that happen when I ran a test of my own, the files created were named exactly right. Any chance you incorrectly edited the DestFLD and left out the final \ in that string? Check the comments in the code above.

[gibson2503]

I'm looking at the code and can't figure out where the "Original" is getting added to the new filenames, it's not included in the new files, nor did that happen when I ran a test of my own, the files created were named exactly right. Any chance you incorrectly edited the DestFLD and left out the final \ in that string? Check the comments in the code above.

Here are those two lines from the VBA code

fileSRC = "S:\PAYROLL\Quimby and Adams\Tracking Spreadsheets\Hybrid and HCW\Hybrid and HCW Sep After 6-30-12\1-Quimby and Adams Tracking(Hybrid and HCW)(Sep after 6-30-12).xlsx" 'template file we are going to copy

DestFLD = "S:\PAYROLL\Quimby and Adams\Tracking Spreadsheets\Hybrid and HCW\Hybrid and HCW Sep After 6-30-12" 'remember the final \ in this string

The original file is

HYBRID AND HCW SEP AFTER 6-30-12.xlsx

The resulting files are named like this

HYBRID AND HCW SEP AFTER 6-30-12SNUFFY,JOE.xlsx

What I want is this

SNUFFY,JOE.xlsx

[JBeaucaire]

Regardless of what the FOLDER path is to the destination, the last character should be a \

I showed that in my sample code and referred to that again in post #4.

Please Login or Register  to view this content.

You need to decide what the final folder is and add that \ at the end of the DestFLD string. The filename will start right after that.

[gibson2503]

This worked out great! Thanks for the help and direction.

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.