VBA to add new year column after last column for all tables, and copy format & equations

Hi,
Current situation
I've several tables within a sheet.
Tables have different columns and rows regarding headers and contents.
Some sheets have 10 tables and others have 30, so I can not determine the exact number of tables within the sheet.
But all tables ending with a column contain a year number plus a word (this word changes from one table to another), the current table has "2016 Balance(Amount)", so other tables may have a different word, but always there is a year then a space, then the word which is "Balance(Amount)" in our example.
Requirements
From time to time I need to add a new column for a new year, so I go through all the tables manually and copy the last column for each table using the black small arrow that appears on the top of the column when you hover the mouse over the top of the first cell of the column, then press Ctrl+C to copy the entire column, then I press the right arrow then press Ctrl+V to paste formulas and format.
So now the newly added column includes the last year's contents, so I do an extra step which is deleting the last year's contents which is associated with the column titled Amount/Equation "This column may be in a different place in another tables, but always will have the same header" where the cell in that row has the word "amount" and keep the contents if the cell has the word "equation".
So the end result will be:

• adding a new column.
• copy only equations to the newly added column.
• clear any other contents from the newly added column.
• end result is: new column for the new year, only equations copied to that column, and all other contents get cleared from the newly added column.

AddNewYearMacro.xlsx

1.png

2.png

3.png

4.png

Extra images for more details.

5.png

6.png

7.png

I hope someone may help.

Somebody...Anybody
simba.jpg

Thanks a lot and much appreciated.

I owe you, my friend.

But I've realized that it adds a new column for tables in other sheets as well.
Is it possible to add a column to the tables within the current sheet only? because I need this to be done within the current sheet only.

Excellent indeed!

Today I've used this macro is a very critical issue at work, and I am blessed that you solved it.
God bless you, my friend.
Thanks a lot for everything.

Hi,
I've about 76 tables and the macro working as Sharpe.
All formulas for all tables are copied forward to the newly added column with new references related to the newly added column.
Please, some particular tables have somehow a complex formula as follows "other formulas look alike but still have the same concept":

Formula:

Please Login or Register  to view this content.

When the new column is added after running the macro, the formula still

Formula:

Please Login or Register  to view this content.

and not forwarded to next year, which should be

Formula:

Please Login or Register  to view this content.

So does there is any adjustment that could be made to let it update the formula for the newly added column and include the new year?

Note: When I drag the formula manually after running the macro, the formula is corrected but I can not guarantee that all other members will do the manual drag for these particular tables as I am not working alone in that file.
So can the drag effect be done using VBA?

AddNewYearMacro.xlsm

Yes, it is exactly.
Thanks a lot
[SOLVED] ... Again!