Macro needed to Add New Column, and Name Header - From Dynamic List.

[SLJones]

Greetings All,

I have an ever growing Dynamic List of Names. Each Name is the Header of a Table Column. There are 12 Tables on 12 Worksheets.
Each time a New Name is added to the Dynamic List, each table must increase by 1 column with the Name as the new header.

I have searched the internets for anything that might help me create a 'simple' macro, OR formula, that will look at the dynamic list, and do my bidding, as a new name is entered... and add a New Named Column to the end of each Table.

I KNOW I came to the right place for answers.

This doesn't require a spreadsheet posting.

Thank you

[jaslake]

Hi sljones

Are these true Excel Tables on each Worksheet or are they simply a normal range of cells?

Where does the Dynamic List of Names live? Are they on a separate Worksheet? What's the Name of that worksheet? What's the Name of that Dynamic Range? Will the new Column Header Name ALWAYS be added on the next available row?

Perhaps a Sample File IS appropriate?

[SLJones]

Thank you jaslake for the reply and WISE suggestion. Nothing is as simple as it seems, I should know!

1. The 'Tables' reside on the monthly sheets. For each Driver (the dynamic list), for each workday, a drop-down list will indicate a specific location.
This information is tracked for each month; (12 tables).
2. The dynamic list (Drivers) resides on the 'Data Sheet', along with the locations, months, and YTD trips.
3. The 'YTD Rept' is the front sheet, which is full of formula to show the trips made, by month and year total. (the [Month] cell is a drop-down for
for each month totals.
------
Drivers come and go; so as each driver is added or deleted... I hope to create a macro that will add a new name to the dynamic list of =Drivers, and also insert a new column to Each table... and/or delete them. Yes, it could be done 1 at a time on each month Table - but that would take all the fun out of using a userform or macro!

[jaslake]

Hi SLJones

This Code is in the attached

Please Login or Register  to view this content.

The Code is in the Data Sheet Module and, as you can see, it's Change Event Code.

A couple things you need to be aware of:

1. I've changed your Sheet Names to conform("Jan." becomes "Jan", "Feb." becomes "Feb", etc, etc)
2. I've changed the Table Name on Sheet Sep from "TableSept" to "TableSep" to conform

As you see, the Code does not run from a UserForm

but that would take all the fun out of using a userform

as there is no UserForm in your WorkBook. Also, there's no Code to delete Drivers

so as each driver is added or deleted...and/or delete them

This "Stuff" can be added as you develop the Project. I believe the above Code address the issue of this Thread.

Let me know of issues.

[SLJones]

Thank you very much jaslake!
You really helped me out immensely.. With just a little tweaking and head-scratching, I was able to get things working just as I hoped.
** (Reputation to you !)

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.