Advice on Automating Task

[Guest]

I have a workbook with 10 sheets.

Sheets 1-5 contain a cost model for a single state. Sheets 5-10 contain
input data to the cost model for 35 different states. Sheets 1-5 have lookup
functions, etc. that access the correct input data in sheets 5-10 based on
the value in a cell, say "state".

What I ultimately want is 35 different workbooks, one for each state. None
of the 35 should have sheets 5-10 (I'll have to do copy, paste values).
Also, none of the 35 should have any macros or named ranges.

What's the best way to do this? I know how to do most of it I think. What
I'm struggling with is...if I delete all sheets 5-10 and save it as "Texas",
then how do I get the original workbook with all of the formatting and
references back? I'm sure there's an elegant way to do this.

 

[Dave O]

Just to make sure I understand this: sheets 1-5 pertain to a single
state that you specify. Sheets 6-10 contain input data for all states.
You want to create 35 files and dedicate one for each state that you
work with.

If that's the case, then can I suggest you create a single file with
the input data, sheets 6-10, so when that data changes you update a
single file, not all 35. You can set things up so the 35 state files
all refer to a single data source file.

If I have all that right, here's some ideas:
~ ***___Make a backup!!!___*** copy of your functional, operational
spreadsheet so you can revert back to it in case you encounter a
serious error, or accidentally delete data, or whatever. 30 seconds of
forethought can save you *days* of recovery. Depending on you like to
run things, you might even save this to a totally separate folder to
make absolutely sure it will not get corrupted.

~ Create a working copy of your spreadsheet and call it "State Master"
or some such. Open up a new blank spreadsheet and Move sheets 6 thru
10 to this new blank spreadsheet. (Hold the CTRL key down and
left-click the tabs for sheets 6-10. These are now "grouped". Right
click any of the grouped tabs and select "Move or Copy". In the "To
Book" dropdown select your state data input filename. Click OK and the
tabs will move to the new file.) State Master will now contain only
tabs 1-5, the cost model; State Input Data will now contain only tabs
6-10, the input data.

~ Save the State Master cost model and the State Data Input file. The
VLOOKUP references in the state cost model will now refer to the State
Data Input file. Make copies from the State Master file for each
state.

Let us know how it goes!

 

[y_not]

Through Windows explorer copy the workbook and paste 35 copies; rename
the copies and amend/delete as you need in eact one.

I don't think that this is what your looking for but it might be.

Good luck

Tony