Hi Rob

Yes it will work.
Just so you can understand the way it works create a the following on a new
sheet
A1 name
B1 Value1
C1 Value2
D1 Formula1
E1 Formula2

In D1 enter = B1*C1
in E1 enter = D1*E1
Enter some values in A2, B2 and C2
Place cursor in A1, DataListCreate Listclick my data has header rowOK

Now on sheet2 enter some appropriate values in A B and C for a few rows.
Copy this datamove to Sheet1 and paste the data into A3 (where there should
be a blue asterisk) and you will see that the formulae automatically get
entered in columns D and E.

If there is no asterisk showing in the next available row of the table in
Sheet1, click on column A of the last row entered, and a blue line will
surround the whole list, including the first blank line following the data,
where the asterisk will appear.

Now if you place your cursor in any cell on the list and choose DataPivot
TableFinish it will use the List as the source, and it will grow
dynamically as more rows of data are added.
--

Regards
Roger Govier

"Gaffnr" wrote in message
...
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