How to tell excel to run a macro when column headings become blank

[billyheanue]

Hi everyone,

I took a stab at making a macro that simply adds a column, copys adjacent cells formatting into it, and copies the adjacent cells column heading into the new one - but changes it slightly. For example, it changes the column heading "Case Price Effective 05.01.15" to "Case Price Effective 08.01.15".

However, I am working with excel sheets that vary in the amount of columns. While the macro works for one sheet, another sheet may have 1 or 2 fewer columns, and the absolute reference of the macro fails to work.

How do I tell excel to run the macro only for the cells to the RIGHT of the last column? For example, the macro would create the 2nd column, but only after seeing that "Case price effective 08.01.15" was the LAST column populated.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl74, width: 64"]Case price effective 08.01.15[/TD]
[TD="class: xl74, width: 64"]Case Price effective 11.01.15[/TD]
[/TR]
[TR]
[TD="class: xl75"]$23.03[/TD]
[TD="class: xl75"][/TD]
[/TR]
[TR]
[TD="class: xl75"][/TD]
[TD="class: xl75"][/TD]
[/TR]
</tbody>[/TABLE]