Dynamic Lists

[russmeister]

I'm looking to create a list of company names that dynamically update on separate sheets as I add new companies. I want the list on one sheet and I want that list to appear on multiple sheets and create a new row that's part of a sorted list each time I add a new company. For Example:

current list on sheet 1, 2, and 3 (sheet 1 is this data only; sheet 2 and 3 have different data sets)
ABC Company
XYZ Company

I update sheet 1 to the following:

ABC Company
XYZ Company
NEW Company

Now, when I make that addition, I want a new row created on sheets 2 and 3 automatically.

Anyone know how to do this?

[walruseggman]

A lot of details are missing in your post, but the basic structure for what you want is below. I tried to comment what you need to modify, based on how your sheets are laid out.

The code belongs in Sheet1's object.

If you need help modifying the code, please post your workbook or an example workbook that mirrors you workbook's formatting.

Please Login or Register  to view this content.

[russmeister]

Attached is an example.Example Dynamic Workbook.xlsx

[walruseggman]

Here you go. Works perfectly on the example you provided and the description you gave. Let me know if you need any modifications.

Please Login or Register  to view this content.

[russmeister]

Thanks! Looks good but one additional step. There are other formulas on the page, specifically sums that take place at the bottom and ends of each row and column. Can those be automatically updated as well to include the added rows? Also, when the rows are added, what do I need to change in the formula so it sorts alphabetically?

[walruseggman]

what do I need to change in the formula so it sorts alphabetically

It should already sort alphabetically, and it does for me. That's what the last part of the code, starting at ws2.Sort.SortFields.Clear, is doing. Like I said, it works for me, can you double check, please?

Can those be automatically updated as well to include the added rows

Yes. See code, you can add the code below right before Application.ScreenUpdating = True near the end of the code.

Please Login or Register  to view this content.

[russmeister]

So I've added the code you have referenced here but it's not updating the sum totals at the bottom under "weekly results" nor is it adding it to the columns for Feb and March in each new row.

I've attached the workbook again here with the updates you've provided.

[walruseggman]

Oops, simple syntax error on my part concerning LastRow2 and LastRow3. Replace the previous code i posted with this:

Please Login or Register  to view this content.

[russmeister]

Look at column H & T. These do not update but the above does fix the weekly results at the bottom.