copying formulas through vb

Hello all and thank you very much for all the support and help you have provided me until now.

I am working on an update of one of my model files and need to make it more "automated".

Here is the problem I am facing.

I have a worksheet which practically has two tables.

One table on the left has a number of columns with data.
The other table on the right has a number of columns with formulas that process the data contained on the left table.

I have put in the formulas on the first row of the right table and what I need to do it this:

As I copy the data on the left table (say 1000 rows of data) I need the right table to automatically extend the formulas down each column until the end of the lines of the left table.

I am facing two problems:

1) The number of lines with data that I copy each time is uncertain and it can range from a few 100's to 1000's therefore I can not set a specific range through vbasic.

2) I do not know how to auto-fill the formulas down the columns on my right table and make the auto-fill stop at the end of the data of the left column. I would prefer to do it with the following sequence:

a) dump the data on the left table
b) have a button that I press and automaticall fills-in the formulas on the right table.

I am not sure if what I try to say is clear but if you need further explanation (or a better example) let me know.


example:
LEFT TABLE (DATA) RIGHT TABLE (FORMULAS)
A B C D G H I
8 9 1 3 FORMULA FORMULA FORMULA
9 3 0 1 (MUST AUTO-FILL THE
7 6 4 6 FORMULAS UNTIL THE
0 9 8 8 END OF THE LEFT TABLE)
.. .. .. ..
.. .. .. ..


Regards,

Kostas

Here is an example of what I want to do.

Hi Kostas

Something like this should work okay

Please Login or Register  to view this content.

Regards


Jeff

Thank you very much,

it seems to be working just fine!

Regards,


kostas

One more question,

If I am using many worksheets in a workbook, how do I modify the macro to do the same job on each worksheet (they will be identical in structure but contain different data values). How do I assign the worksheet name within the macro ?

Regards,

Kostas