macro help - copying a list of names

[mastersparky]

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

 

[Barb Reinhardt]

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.Search("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

 

[ShaneDevenshire]

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("Sheet",CELL("filename",A2))+5,4),0)

Done. Break group mode by clicking on the Index sheet.

 

[Rick Rothstein]

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 Rothstein]

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).

 

[mastersparky]

WOW

Thanks guys (and gal) for all your help

worked AWESOME - saved me tons of time

mastersparky