I have one worksheet (let's call it Investor 1) with formulas looking at a
few other worksheets for the data. I have numerous other worksheets (each
one is another investor) that need to look at the same worksheets that
investor 1 is looking at, but the formulas on each new investor worksheet
need to change. Excel does this for you automatically if you are on the same
worksheet, but when you create multilpe worksheets how can you fill the data
across each new worksheet and make the formulas adjust properly.

Simple example: Investor 1 worksheet: In cell b6 is ='sheet1'a2. On
Investor 2 worksheet in cell b6 it needs to be ='sheet1'a3. On Investor 3
worksheet in cell b6 it needs to be ='sheet1'a4. Can you do this with fill
data?

Sub Formula_Increment()
Dim iSht As Long
For iSht = 2 To Worksheets.Count
With Worksheets(iSht).Range("B6")
.Formula = "=Sheet1!A" & iSht
End With
Next iSht
End Sub


Gord Dibben MS Excel MVP

On Fri, 22 Feb 2008 09:51:02 -0800, heater
wrote:

I have one worksheet (let's call it Investor 1) with formulas looking at a
few other worksheets for the data. I have numerous other worksheets (each
one is another investor) that need to look at the same worksheets that
investor 1 is looking at, but the formulas on each new investor worksheet
need to change. Excel does this for you automatically if you are on the same
worksheet, but when you create multilpe worksheets how can you fill the data
across each new worksheet and make the formulas adjust properly.

Simple example: Investor 1 worksheet: In cell b6 is ='sheet1'a2. On
Investor 2 worksheet in cell b6 it needs to be ='sheet1'a3. On Investor 3
worksheet in cell b6 it needs to be ='sheet1'a4. Can you do this with fill
data?