should have been

With Range("D5:D50")
.FormulaR1C1 = Range("D1").FormulaR1C1
.Value = .Value
End With


"BEEJAY" wrote in message
...
Hello:
Am trying to "auto fill" a look up table.
Have a number of sheets that do 'behind the scenes' calculations.
The length determines what the $'s will be.
On my overview sheet, making up a look-up table.
B1 is to be used for length input
D1 will provide the $ value, based on the length in B1
B5 thru B50 is the lengths, in feet, low to high

What I'm picturing is code that will:
Copy B5 to B1. The D1 Value is then changed - Copy and PasteValue to D5.
Copy B6 to B1. The D1 (changed) Value is then Copy and Paste Value to D6.
Etc............
Since there will not be any formulas in the look-up chart, I can then copy
the resulting chart to all the required Work-Books.
Somehow, the program should also (self) determine the last row, since this
macro would be used numerous times, almost always with different lengths
of
column.

I have a basic recorded macro started, but it requires a loop, which I
understand is not the most efficient way of doing something like this.

The requirement for this process is numerous, so I'm muchly looking
forward
to being able to automate this.

Thanks.

Patrick:

Thanks for the responses.
The 1st method works great!, as shown.

I'm trying out the (revised) 2nd method, but cannot get it to operate.
It appears to require some additonal code, but I have no idea what.
I presume some combination of the two?.

Could you please advise what is required? For starters, there doesn't seem
to be anything that copies the lengths from column D into B1, which in turn
would then trigger the newly calculated $ value.

Thank you

"Patrick Molloy" wrote:

should have been

With Range("D5:D50")
.FormulaR1C1 = Range("D1").FormulaR1C1
.Value = .Value
End With


"BEEJAY" wrote in message
...
Hello:
Am trying to "auto fill" a look up table.
Have a number of sheets that do 'behind the scenes' calculations.
The length determines what the $'s will be.
On my overview sheet, making up a look-up table.
B1 is to be used for length input
D1 will provide the $ value, based on the length in B1
B5 thru B50 is the lengths, in feet, low to high

What I'm picturing is code that will:
Copy B5 to B1. The D1 Value is then changed - Copy and PasteValue to D5.
Copy B6 to B1. The D1 (changed) Value is then Copy and Paste Value to D6.
Etc............
Since there will not be any formulas in the look-up chart, I can then copy
the resulting chart to all the required Work-Books.
Somehow, the program should also (self) determine the last row, since this
macro would be used numerous times, almost always with different lengths
of
column.

I have a basic recorded macro started, but it requires a loop, which I
understand is not the most efficient way of doing something like this.

The requirement for this process is numerous, so I'm muchly looking
forward
to being able to automate this.

Thanks.