ActiveCell.Column

will give you the column number of the cell that is currently the
activecell.

Not sure how you are referencing, but instead of hardcoding column AD in
your reference, use a method that can utilize information from the
activeCell

for example, instead of Range(AD5)
use cells(5,ActiveCell.column)

--
Regards,
Tom Ogilvy

"John" wrote in message
...
I have a spreadsheet that is a weekly P&L that combines at the very end.
So
it has 52 columns on it that are labled week ending **/**/****. I can
export
the data from my accounting program into a spreadsheet but it is not in
the
same format as the worksheet we currently keep the weekly tracker in. The
accounting program exports weekly to the same file name it never changes.
I
wrote a macro in cell AD to pull the info from the named worksheet and all
the way down the column. Totals out and looks just like the printed P&L.
Problem is Next week is Column AE.. I run the macro and it just inserts
over
AD. I need it to be an absolute cell reference for the exported worksheet
from the accounting program but an active cell paiste to whatever column I
put the cursor in before running macro.

I wanted to invert the worksheet so that I could just do current week in
Column 1 and when the macro runs insert column to move everything over.
Problem is the bosses have used this same worksheet forever and like the
current format.

Any help???