inserting and naming a tab with vba - any help please thank you

[Eduardo]

I have excel 2007, I have a list of products in column A rows 5 to 19 ( with
possible new additions) and I need a macro to insert tabs and name its with
the product names. Everytime the macro is run it will identify the new
prroducts and will create a tav for it. Each tab will be a copy or the master
tab (where I have the formulas and calculations) and will copy the name of
the tab in Cell B1. I appreciate any help. thanks

 

[JP]

First you want to defined a dynamic named range (if possible) for the
column that contains the product list. Then you want a macro to loop
through the range and check if there is an existing worksheet with
that name. If not, create the worksheet and name it with the product
name, copy over the information from the master worksheet, and copy
the name of the worksheet into cell B1.

Do you have any code written at all?

--JP

 

[Eduardo]

Hi JP thank you for answering,
I tried the one as follow for creating a tab but is not working. I got this
code from the comunity, maybe the problem is the excel version. Is giving an
error message when it's run

Run-Time error 9 - Subscript out of range

Sub Macro2()
'
' Macro2 Macro
'
With Sheets("sheet1") 'where list is in col A
For i = .Cells(.Rows.Count, "a").End(xlUp).Row To 2 Step -1
Sheets.Add.Name = .Cells(i, "a")
Next
End With

'
End Sub

 

[Mike H]

Hi,

A couple of assumptions. The sheet you want to use as a template is called
"Base Data" change this to suit.

The list of worksheets you want creating is in column A of a sheet called
"Menu" Also change these to suit.

Right click any sheet tab, view code and paste this in and run it

Sub stance()
Sheets("Base Data").UsedRange.Copy
Dim ws As Worksheet
Dim myrange As Range
Lastrow = Sheets("Menu").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Menu").Range("A1:A" & Lastrow)
For Each c In myrange
On Error Resume Next
Set ws = Sheets(c.Value)
If ws Is Nothing Then
Sheets.Add.Name = c.Value
Sheets(c.Value).Range("A1").PasteSpecial Paste:=xlPasteAll
End If
Next
End Sub


Mike

 

[Eduardo]

Hi Mike,
That worked except that some of the sheets were named with # instead of the
name. As follow is the list of products

1Add-On Tools
2Custom Solutions
3SAP
4Env Geo Apps/Tools
5Maintenance
6Min Ex Apps/Tools
7Oil & Gas Apps/Tools
8Training
9TSA
10Uxo Apps/Tools
11Geosoft Oasis montaj
12Metech acQuire
13Geosoft DAP
14Geosoft Target
15Services

the number at the beginning was entered by me just to tell you which ones
the tab was not named. That were 10 - 7 - 6 - 4

Thank you

 

[Eduardo]

Hi Mike,
From my last post I figured out what was the problem, (special characters
like / )and fixed it, however when I add new products and try to run the
macro again it doesn't create a tab for the new products. Can you help me.
Thank you