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

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

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

This macro should do what you want...

Sub AssignNames()
Dim X As Long
For X = 7 To 221
Worksheets("Sheet" & (X - 6)).Range("C1").Value = _
Worksheets("Index").Cells(X, "B").Value
Next
End Sub

--
Rick (MVP - Excel)


"mastersparky" wrote in message
...
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

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

WOW

Thanks guys (and gal) for all your help

worked AWESOME - saved me tons of time :)

mastersparky






"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