creating and naming sheet based on template

Microsoft Office access 2007 (full produ...
July 9, 2010 at 04:05:03
Specs: Windows XP
Hi all,
I have some code below to create new sheets from a template and name them accroding to a cell range which is specified in the code, perhaps it is because I am asking it to do too many things at once, but the code is creating one sheet based on the template specified, and then just renaming it to the name range, rather than creating a new sheet for each one.

Any help would be appreciated.

Sub Sheetcreationbycellvalue()

Dim Sh As Worksheet
Dim shName As String
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet1").Range("A1:A201")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

'name of the sheet template
shName = "WHSCT.xltm"

'Insert sheet template
With ThisWorkbook
Set Sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
Before:=.Sheets(.Sheets.Count))
End With


On Error Resume Next
For Each MyCell In MyRange
Sh.Name = MyCell.Value
If Err.Number > 0 Then
MsgBox "Change the name of Sheet : " & Sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next MyCell
End Sub


See More: creating and naming sheet based on template

Report •


#1
July 9, 2010 at 04:22:49
Hi,

Your code starts by setting some ranges then it creates one worksheet with this line:

Set Sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
		Before:=.Sheets(.Sheets.Count))

The code then loops through the cells containing names, applying the names in turn to the same worksheet, here:

For Each MyCell In MyRange
	Sh.Name = MyCell.Value
	...
next MyCell

You need the code to create the worksheet inside the For Each MyCell .... Next MyCell loop.

Regards


Report •

#2
July 9, 2010 at 04:26:10
How did I miss that,

Many thanks Humar


Report •

#3
July 9, 2010 at 06:10:05
You're welcome

Regards

Humar


Report •
Related Solutions


Ask Question