create 50 copies of a worksheet in the same file
 

I want to copy a certain worksheet within xcell, 50 more times. I don't want
to do it one at a time. Is there a command to copy it multiple times?

After setting up the multiple copies, I want to name them from a reference
list inside the same workbook, so that the tab name appears from a name
entered in a cell location, from a directory of names. Is this possible?

I would like the worksheet tabs to flow from left to right in alphabetical
order automatically, as entered, or be able to sort the tabs in alphabetical
order.

create 50 copies of a worksheet in the same file
 

This assumes that your list of worksheet names is on the sheet you want copied:
1) select the range that contains the workksheet names you wat applied
- make sure the names are sorted alphabetically
2) right click on the tab for the worksheet you want copied and choose "view
code"
3) paste in this code, then click anywhere in the body of the code
4) press the F5 key


Option Explicit

Sub CreateWorksheetCopies()
Dim rng As Range
Dim wsOrig As Worksheet
Dim ws As Worksheet
Dim strName As String

Set wsOrig = Me
strName = Me.Name
Application.ScreenUpdating = False
For Each rng In Selection
ThisWorkbook.Worksheets(Me.Name).Copy After:=Worksheets(strName)
' if you want to copy a sheet other than the one with sheet names,
' change Me.Name above to the name of the sheet to copy, enclosing it in
double
' quotes
ActiveSheet.Name = rng.Value
strName = rng.Value
wsOrig.Activate
Next
End Sub

create 50 copies of a worksheet in the same file
 

Add this code to the page with the names and kill two birds with one stone:

i = 1
Do Until Cells(i, 1) = ""
Sheets.Add
ActiveSheet.Name = Cells(i, 1)
i = i + 1

set i equal to the row the names start on and change the 1 to the column
number they are in. I posted a link somewhere around here to sort tabs.
Where was that?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Lynn" wrote:

I want to copy a certain worksheet within xcell, 50 more times. I don't want
to do it one at a time. Is there a command to copy it multiple times?

After setting up the multiple copies, I want to name them from a reference
list inside the same workbook, so that the tab name appears from a name
entered in a cell location, from a directory of names. Is this possible?

I would like the worksheet tabs to flow from left to right in alphabetical
order automatically, as entered, or be able to sort the tabs in alphabetical
order.

create 50 copies of a worksheet in the same file
 

Sorry, I got the mistaken impression you were making blank sheets with names.

check out Chips site for sorting sheets


http://www.cpearson.com/excel/sortws.htm

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Bundy" wrote:

Add this code to the page with the names and kill two birds with one stone:

i = 1
Do Until Cells(i, 1) = ""
Sheets.Add
ActiveSheet.Name = Cells(i, 1)
i = i + 1

set i equal to the row the names start on and change the 1 to the column
number they are in. I posted a link somewhere around here to sort tabs.
Where was that?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Lynn" wrote:

I want to copy a certain worksheet within xcell, 50 more times. I don't want
to do it one at a time. Is there a command to copy it multiple times?

After setting up the multiple copies, I want to name them from a reference
list inside the same workbook, so that the tab name appears from a name
entered in a cell location, from a directory of names. Is this possible?

I would like the worksheet tabs to flow from left to right in alphabetical
order automatically, as entered, or be able to sort the tabs in alphabetical
order.

create 50 copies of a worksheet in the same file
 

Lynn,

Another option to try ..

Run the Sub CreateNameSheets() below
(Sub is by Dave Peterson)

Here's how to set it up ..

In a copy of your book,

The sheet: Template
would be your template sheet as prepared
(You need to re-name that "certain" sheet to be replicated as: Template)

In a sheet named: List,
enter (or paste over) the reference list of 50 sheetnames in A1 down
(You need to name this sheet as: List)

The sub will copy the sheet named as: Template
and create & name the new sheets
according to the list in col A in List

Steps
--------
Press Alt+F11 to go to VBE
Click Insert Module
Copy paste everything within the dotted lines below
into the code window (whitespace) on the right

'-------begin vba-----
Sub CreateNameSheets()
' by Dave Peterson
' 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 = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell

End Sub
'-------endvba------

Press Alt+Q to get back to Excel

In Excel, press Alt+F8 (brings up the Macro dialog)
Select "CreateNameSheets" click "Run"
(or just double-click directly on "CreateNameSheets")

The sub will produce all the 50 copies of "Template" to the right,
naming these according to the 50 sheetnames in "List"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lynn" wrote:
I want to copy a certain worksheet within xcell, 50 more times. I don't want
to do it one at a time. Is there a command to copy it multiple times?

After setting up the multiple copies, I want to name them from a reference
list inside the same workbook, so that the tab name appears from a name
entered in a cell location, from a directory of names. Is this possible?

I would like the worksheet tabs to flow from left to right in alphabetical
order automatically, as entered, or be able to sort the tabs in alphabetical
order.

create 50 copies of a worksheet in the same file
 

In a sheet named: List,
enter (or paste over) the reference list of 50 sheetnames in A1 down

The 50 copies will be created (from left to right) in the order that the
names are listed in A1 down. So just sort the list in A1:A50 in the desired
order before running the sub.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---