Hardcoding doesn't work when row is deleted

[nicolehalliday]

Hi!

I have 110 columns of data that are 500 rows long. My macro pastes the most recent column of data at the right end of the table, and then deletes the least recent column which is the most left of the table. It is basically rolling over every day to contain only 110 columns.

I am having problems with hardcoding columns. In the separate spreadsheet that I take my most recent column of data for, I have the following cell:

=WORKDAY(highs!$EE$1,1)

The date that returns in this cell determines the data that is copy/pasted into the table. However, after one run of my macro the cell turns into:

=WORKDAY(highs!$ED$1,1)

I thought the $ signs would hard code it so that no matter what, it is referencing the date in EE1. It is not working because I keep deleting column Z in my macro. Is there a way to do this? Thanks!

 

[JackDanIce]

Can you post your code please?

 

[Jonmo1]

Ideally, try not deleting columns/rows.
Try copy/pasting new data on top of the old data...

Otherwise, you can Try

=WORKDAY(INDEX(hights!$1:$1,135),1)

 

[nicolehalliday]

Thanks for the replies! I thought I'd avoid posting code if not necessary bc its so long. The INDEX function works like a charm, much appreciated!!!