Excel VBA - create worksheets/dynamic range names problem

[MaxBrit]

Hello

Some help or a pointer to where we can find a solution please.

We have an Excel 2002 workbook, with a list,dynamic named range.

For each item in this list we wish to copy a template worksheet an
name it to the value in the list.

On each new worksheet to create a dynamic range and give that the nam
in the list.

So if list was ProdA, ProdB, ProdC, would have corresponding sheets an
on sheet ProdA would have dynamic range named ProdA, on sheet ProdB
range ProdB and so on.

Sub CopySheets()
'
'For each product code entered in range create a worksheet based on th
template
'Rename sheet = product code
'Create a range name on each sheet to look up used parts




Dim c As Range

For Each c In Range("code_list")

Sheets("template").Copy After:=Sheets("template")
Sheets("template (2)").Name = c.Value & "1"

Next c

End Sub

Unable to get sheet name = to the value in the list.
Unable to see how to create a dynamic range for each sheet = produc
code.

Thanks for any help/advice.

Ma

 

[Tom Ogilvy]

Sub CopySheets()
'
'For each product code entered in range create a worksheet based on the
template
'Rename sheet = product code
'Create a range name on each sheet to look up used parts




Dim c As Range

For Each c In Range("code_list")

Sheets("template").Copy After:=Sheets("template")
ActiveSheet.Name = c.value
Range(Range("A1"),Range("A1").End(xldown)).Name = c.Value
Next c

End Sub

That is a named range dynamically determined - Do you mean the refers to
part of the name is a formula?

 

[MaxBrit]

Thanks for the reply

On each worksheet trying to achieve the below. Dynamic range set t
sheet name and set to component:qty range so the user can ad
components into column. Will then lookup components from master shee
to get total requirements. Will have varying components for eac
assembly, template consists of titles and 1 dummy data line to enabl
formulas to be set up.

Worksheet - ProdA

Component Qty
CompA
CompB
CompC

Worksheet - ProdB

Component Qty
CompA
CompC

Hope you can understand my explanation of what we are trying to ge
to.

Thanks
Ma

 

[Tom Ogilvy]

You might need to make a few adjustments to the formula, but this basically
does what you describe:

Sub CopySheets()

Dim c As Range

For Each c In Range("code_list")

Sheets("template").Copy After:=Sheets("template")
ActiveSheet.Name = c.Value
ActiveWorkbook.Names.Add Name:=c.Value & "!" & c.Value, _
RefersToR1C1:= _
"=OFFSET(" & c.Value & "!R1C1,0,0,COUNTA(" _
& c.Value & "!C1),2)"
Next c

End Sub

 

[MaxBrit]

Tom

Many thanks for you help.

Ma