VBA to insert missing columns based on a list

[Phil123456789]

Hi,

I am struggling with a macro that is supposed to compare 10 columns of a sheet to a list of column headers, and add the columns that are missing (with just blank values).

The problem is that the list is dynamic, with different column headers depending on which "company" is chosen in a cell ("T5"; I am aware that the cell moves around in the sheet when columns are inserted, but in the real workbook the company dropdown is located in a different sheet).

I have adapted the code from here https://stackoverflow.com/questions/...ed-from-a-list, but cannot seem to get the dynamic element to work.

Here is the code I have used so far:

Please Login or Register  to view this content.

The problem seems to be the

Please Login or Register  to view this content.

part, where I get a run-time error '13' (type mismatch).

Any suggestions? Thanks

[Fluff13]

How about

Please Login or Register  to view this content.

[Phil123456789]

Thanks Fluff, seemed to do the trick

However, it seems that the code adds columns that already exist, so long as they appear in a different order than in the table.

Is there a way around this? E.g. if "Column 4" appears in Column D, it is still added if "Column 4" is e.g. the first row in the list

[Fluff13]

How about

Please Login or Register  to view this content.

[Phil123456789]

Perfect, great stuff.

One last question (which is not relevant right now but might be soon), if it requires just a simple tweak.

If I wanted the sheet to delete all columns that do not appear from the list, what would I have to do?

[Fluff13]

How about

Please Login or Register  to view this content.

[Phil123456789]

Yes, thanks! Except it removes the very last column that it just added

[Fluff13]

Sounds like there weren't any extra columns to remove, try

Please Login or Register  to view this content.

[Phil123456789]

That tweak simply adds back all (both deleted and kept columns) to the right of the last column.

Last code worked perfectly, except it removed the very last column that it added/kept

[Fluff13]

Not for me it doesn't, do you have anything in row1 other than the headers?

[Phil123456789]

Nope - error occurs once you run the code twice (works fine first time, but if you run it again without changing anything it bugs)

[Fluff13]

Nope, I can't replicate that behaviour, so not sure what's happening.

[Phil123456789]

Hmm, weird. I will find a solution.

Anyways, thanks a lot for all the help, greatly appreciated!

[Fluff13]

You're welcome & thanks for the feedback.