Try this
Option Explicit

Sub Test()
Dim myIndex As Excel.Worksheet
Dim myRange As Excel.Range
Dim isInstr As Boolean
Dim SheetNo As Long
Dim i As Long
Dim myWS As Excel.Worksheet

Set myIndex = ThisWorkbook.Worksheets("Index")
Set myRange = myIndex.Range("B7:B221")

For i = 1 To ThisWorkbook.Worksheets.Count
Set myWS = ThisWorkbook.Worksheets(i)
isInstr = False
On Error Resume Next
Debug.Print myWS.Name, WorksheetFunction.Search("Sheet", myWS.Name)
isInstr = WorksheetFunction.IsNumber(WorksheetFunction.Searc h("Sheet",
myWS.Name))
On Error GoTo 0
Debug.Print isInstr, myWS.Name

If isInstr Then
SheetNo = Val(Replace(myWS.Name, "Sheet", ""))
Debug.Print SheetNo
Debug.Print myRange.Cells(SheetNo).Address(External:=True),
myRange.Address
Debug.Print myWS.Range("C1").Address(External:=True)
myWS.Range("C1").Value = myRange.Cells(SheetNo).Value
End If
Next i
End Sub
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"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