Another possible approach to placing formulas directly into the cells
themselves, but one which greatly simplifies the formulas that are placed in
the cells from those which your method requires, is to run the following
macro one time....

Sub AssignFormulaForIndexSheetNames()
Dim X As Long
For X = 7 To 221
Worksheets("Sheet" & (X - 6)).Range("C1").NumberFormat = "General"
Worksheets("Sheet" & (X - 6)).Range("C1").Formula = "=Index!B" & X
Next
End Sub

After the macro has been run, cell C1 on each worksheet named SheetX (where
X is a number between 1 and 215), will contain the formula =Index!BY (where
Y is a number between 7 and 221).

--
Rick (MVP - Excel)


"ShaneDevenshire" wrote in
message ...
Hi,

You don't need a macro for this you can write a formula.

I will use 250 as the number of sheets to be named in this example.

I am assuming that your sheets are named as you stated Sheet1, Sheet2,
....
with your names in the range B7:B257 on the sheet named Index

1. Select Sheet1
2. Use the tab scrollers to view the last sheet
3. Hold down the Shift key and click the tab of the last sheet. (you
should
now be in Group mode with Sheet1 to Sheet250 selected)
4. In cell A1 type the following formula.
=OFFSET(Index!B6,MID(CELL("filename",A2),FIND("She et",CELL("filename",A2))+5,4),0)

Done. Break group mode by clicking on the Index sheet.
--
Thanks,
Shane Devenshire


"mastersparky" wrote:

I have a list of names in an excel spreadsheet, specifically in column B
of a
worksheet called 'index' (the range of the list is B7:B221) I am trying
to
copy or link each name to a specific location on each of the 215
worksheets
(cell C1 on each worksheet) The worksheets are consecutively numbered
starting from sheet1, so the name from index!B7 gets copied to sheet1!C1;
index!B8 to sheet2!C1; index!B9 to sheet3!C1; etc., etc., all the way
down
the list. Does anyone know of a macro to help me with this?? I am using
Office 2003 Any help would be greatly appreciated

Thanks

mastersparky