Create Named Worksheets

[Guest]

Hello all,

I have read some posts regarding creating named worksheets but I am unable
to get it to work properly for me. I am using the macro from Dave Peterson.
My "List" contains the following in column A......JAN 07, FEB 07, MAR 07 etc.
formatted in a text format. I want the macro to create and name the
worksheets as listed above but it won't work. I'm sure I'm doing something
dumb.....lol......can someone help? Thanks.

Ron

 

[Bob Phillips]

It might hep to post the code that you are using.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JW]

Number of different ways to handle this depending on where you want
the sheets inserted into the sheet structure. The code below will
cycle through all of the cells in column A, starting from the bottom
up and running until row 2, and add new sheets to the beginning of the
workbook. This will keep the sheets in the same order as they appear
in column A.
Sub createSheets()
Dim mainSheet As Worksheet
Set mainSheet = ActiveSheet
With mainSheet
lRow = .Range("A65536").End(xlUp).Row
For i = lRow To 2 Step -1
Worksheets.Add(Before:=Sheets(1)).Name = .Cells(i, 1).Text
Next
End With
Set mainSheet = Nothing
End Sub

 

[Guest]

Hi Bob,

My apologizies for the late response. I caught a dang bad bug and got laid
up in the hospital for a bit. Below is the code I am using. I definetely
need to have each worksheet created using a template. Any help would be
greatly appreciated. Thanks.

Ron

Sub CreateNameSheets()
' by Dave Peterson
' (slightly revised by Max to format sheetnames <g>)
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = Format(myCell.Value, general)
If Err.Number <> 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub

 

[Dave Peterson]

You deleted "Option Explicit"!!

If you hadn't deleted that line, you would have been yelled at about this line:
ActiveSheet.Name = Format(myCell.Value, general)

General looks like it should be a variable and since it isn't declared, it'll
cause an error.

But even if you had used:

ActiveSheet.Name = Format(myCell.Value, general)

It' wouldn't have worked. VBA's Format and Excel's =Text() aren't
interchangeable.

But if those values on that List worksheet are really text values, then you
could use:
ActiveSheet.Name = myCell.Value
or
ActiveSheet.Name = myCell.Text

If they're really dates, you could use:
ActiveSheet.Name = myCell.Text 'if they're formatted nice
or
ActiveSheet.Name = Format(myCell.Value, "mmm yy")
(if Jan 07 represent January 2007?)

=======
If none of this helps, you may want to be more specific about what goes wrong.

 

[Guest]

Dave,

Thank you very much for your help and explanation. Everything works as
expected now. I thought it had something to do with the "Text" formatting
but I have very limited experience with VBA code and was unable to fix it
myself.

Also, once again, I want to thank all of you who help out others on this
forum. I for one always appreciate the prompt help and replies to any
questions I have. Thank you all.

Ron