fill a particular cell on a series of worksheets from a lookup tab
 

In the first worksheet of my workbook I have a list of part numbers listed in
a column, say approximately 20 part numbers. I would like to have one
worksheet for each part number, with cell A1 containing the part number for
that worksheet.

So if my list was:

pn1
pn37
pn469
pn2034

then cell A1 of the first worksheet in the series would ="pn1", cell a1 in
the second worksheet of the series would ="pn37"

Is there an easy way to populate these worksheets from the list?

Thanks,

Rob Samples

You could use a little macro:

Option Explicit
Sub testme()
Dim testWks As Worksheet
Dim MstrWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set MstrWks = Worksheets("sheet1")
With MstrWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
For Each myCell In myRng.Cells
Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(myCell.Value)
On Error GoTo 0

If testWks Is Nothing Then
'add it
With Worksheets
Set testWks = .Add(after:=.Item(.Count))
On Error Resume Next
testWks.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Illegal name: " & myCell.Value & vbLf & _
"Please correct worksheet named: " _
& testWks.Name
Err.Clear
End If
On Error GoTo 0
testWks.Range("a1").Value = myCell.Value
End With
End If
Next myCell
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


br549 wrote:

In the first worksheet of my workbook I have a list of part numbers listed in
a column, say approximately 20 part numbers. I would like to have one
worksheet for each part number, with cell A1 containing the part number for
that worksheet.

So if my list was:

pn1
pn37
pn469
pn2034

then cell A1 of the first worksheet in the series would ="pn1", cell a1 in
the second worksheet of the series would ="pn37"

Is there an easy way to populate these worksheets from the list?

Thanks,

Rob Samples

--

Dave Peterson