Hi Roger
Thanks for your superfast reply. Im not sure I understand yet what you
wrote so let me tell you about my end goal to see if what you propose will
help.

The data is populated via an external source, copied and pasted into Excel.
The macro I want is only part of a larger macro. The goal is to put the
ssheet on a shared drive so that my organisation can run this independently
with their own data.

So, they will run the external source report I create.
They will copy and paste their data.
They will then run a macro from a button in the spreadsheet to copy the
formulae down to last row, do some other stuff and create pivot.

The next user will repeat and their data could be more or less rows every
time.

We are using Excel 2003 SP 2. If your first proposal will work, thats
sounds great.
A little more background would be great.

Thanks again
Rob




--
Rob Gaffney


"Roger Govier" wrote:

Hi Rob

Depending upon the version of XL you are running, you may not need to do
this.
For XL2003 there is a feature called DataLists
If you click within your used table range and choose DataList then Excel
will create an ever expanding table, where any formulae are automatically
created on lines added.
This List can be used as the source data for the Pivot Table and it will
grow automatically to include the last row used.

In XL2007, the feature is further improved and can be found on the Insert
tab, as InsertTable.

If you are using XL2002 or lower post back and we can provide you with a
macro solution.

-----
Regards
Roger Govier

"Gaffnr" wrote in message
...
Hi All,
Question re. macros. Im not a programmer thats writes them but someone
who
simply records and plays. Got a question about something....

Lets say I have formulae in cells D2, E2 and F2.
I have data in A2, B2 and C2 down to A10, B10 and C10.

Record Macro.
I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data
rows.
This is fine when I run the macro where the data is always to row 10.
what about if my data in a,B,c goes down to Row
a) row 20 - i am left with 10 rows where the formulae does not copy
b) row 5 - I have rows of formula not needed (as i pivot table the results
this is a pain).

What I want it every time i run the macro to copy the formulae down to the
last row of data, where everit may be.

When i do it manaully, i get the mouse pointer into the bottom righ corner
of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into
a
small black cross and i double click to copy the formula down to the last
row
where there is data in c. This is what I want the macro to achieve. I
tried
copying this mouse stroke but it always on running goes downt to the same
row
every time.
Thanks
rob
--
Rob Gaffney


--
----
Regards
Roger Govier