Automatically copy formulas to last row 1

[wayneryeven]

Hey all
I have a worksheet that is updated from an external source. Suffice to say the number of rows change from day to day. How i have a formula to column E- i want this copied down to the last row of data (i intend to attach the code to Workbook Open). This way, i dont have column E filled with Errors each time if number of rows is less than what i had anticipated.

What is the best way to achieve this? I have started by finding the last row:

'calculate last row
LastRow = ActiveSheet.UsedRange.Rows.Count

I was hoping then to select from E2:Last row and copy the formula from E2 down to the last row- however im not having much joy at the moment!

Range("E2:E" & LastRow).Select


Anyone any suggestions?

 

[SkipVought]

Hi,

"I have a worksheet that is updated from an external source."

If you are using Data > Import external data..., you can set a switch to automatically do that. Data > Import esternal data > Data range properties - check the box at the bottom. Each time you refresh your data, the formulas in column(s) adjacent to the import data will automatically adjust (+/-) to the returned rows.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wayneryeven]

hey Skip
I seen other posts before i posted regarding that, however this is not how the data gets "into" the worksheet. Unfortuantely i cant change how it does come in, so that unfortunately isnt an option?

 

[SkipVought]

Well, how does the data get imported?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wayneryeven]

it gets dumped in via an automated queue from an System 21 ERP system? Unfortuantely i have no access to modify this (and it is v restrictive on what you can do)?

 

[SkipVought]

Dump it into a DIFFERENT sheet.

Then Query that sheet, using the technique described for Data Range Properties.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

FYI, faq68-5829

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}