Listbox Linked to Workbook Tabs (2007)

Hello,

I hope I can explain what I am trying to accomplish properly. Many years ago (5+), I had this exact workbook setup, but am having a lot of difficulty remembering VB & Excel and making this again from scratch.

I am looking to create an Excel Workbook. The workbook will contain numerous tabs, each tab will essentially be a template, and contain basic formulas.

On tab 1(Sheet1), I would like to have a listbox. The individual items in the listbox, should have the same name of, and be linked to all tabs in the workbook.

There will also be three buttons.

Button1 - Generates the names in the listbox based on existing tabs within the workbook
Button2 - Opens a new workbook, and copies over the selected tabs (AKA - creates a working copy of the workbook)
Button3 - Clears the selection and resets the listbox.

The number of tabs, and contents of tabs, within the workbook will be changing. So the listbox should know to look for all tabs, be it 5, or 50.

The end result would allow someone, who knows nothing of Excel, to open this workbook, as a read-only file. Generate a list of available tabs, select the tabs they need, generate a new workbook containing these tabs, that they can then edit to suit their needs.

In my line of work (and I am sure everyones) there is a huge problem with consistancy. This leads to a snowball effect of problems carrying over, and being copied from one project to the next. As I mentioned earler, I once already had this workbook setup, and it solved all our consistancy problems. I am hoping to do the same.

If anyone has any information, can help with code, or point me in the right direction, it would be greatly appreciated.

Roy,

I attached my current workbook.

I used the second version of your posted code. Here's whats happening:

When I click the button, it creates a new workbook. However, it only copies over one of the selected sheets.

If my tabs are:

"Start here, a, b, c, d, e, f, g"

On "Start here", I bring up the listbox, I select say, "A, B, C", then hit the button, "cmdGenerate" (button we're working on). That does the following:

Creates a new workbook, names the workbook the name of the directory that the VB project/excel template exists in, appends the letter, "A" to the end of the name (I assume it's appending the name of the sheet), and only copies over sheet "a", and ignores B, and C.

Any ideas?

We work with project numbers. Would it be possible to have a text box somewhere where a person could input a 4-5 digit code, click the cmdGenerate button, and have it name the file along the lines of:

#### - Schedules.xls (where #### is the number provided by the user)

Thanks again!

Roy,

It is creating multiple workbooks.

Meaning, if I select "A,B,C". It creates three new workbooks, and closes each one. I'd rather have it not close anything if possible


I'd like to see it place all selected sheets into a single workbook, and leave everything open.

Also, what about the ability to manually name/save the workbook? People will be using this template off of a network drive, and saving their "new" workbook, to a specific location (based on project number). If at all possible, I'd like to have it do the following once the cmbGenerate button is clicked:

- Close the listbox (same as clicking cancle), more or less returning the template with the VB code to it's original state.
- Generate the new workbook with sheets
- Prompt a Save-As on the new sheet, and have the name already specified, but require the user to specify a location. The name should be in the format I mentioned earlier (possibly using that method mentioned earlier) - "#### - Schedule.xls".

Having it automatically save down to the users computer will lead to confusion among the less computer saavy here. "Where did it go? How can I find it? Is it on the network...wawawa"

You've been extremely helpful, thanks again.

Works like a champ. There are a few custom issues I think I need to attend to, but this has definatly put me on the right track.

Thanks again!