Macro to copy and rename sheets from list.

[jnels45]

Hello. I need some help.
I have a workbook currently with two worksheets. The 1st sheet has a list of what I want to name all the work sheets. the 2nd sheet will be the format I want all the sheets to look like. Is there anyway I can create a code that will rename sheet 2 to the first name in the list on sheet 1 and then create a new sheet 3 with identical copy of sheet 2 and name that one the 2nd name on list and so forth until it reaches the end of the list on sheet 1?

It seemed simple in my head but I can't get the code right. Can anyone help me?

 

[mumps]

The following macro will loop through the list of names in "Sheet1" and then copy "Sheet2" for each name using the names form the list. It assumes that your names are in column A starting at row 2.

Sub AddSheet()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim ws As Worksheet
    For Each c In Range("A2:A" & bottomA)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        On Error GoTo 0
        If ws Is Nothing Then
            Sheets("Sheet2").Copy After:=Sheets(Sheets.Count).Name = c.Value
        End If
    Next c
    Application.ScreenUpdating = True
 End Sub

 

[jnels45]

I get an error when using this code. error 400

"Sheet1" has the list; data starts on A2 ***IE: list is Dates except Sundays**

"Sheet2" has the format of what I want on every worksheet. A simple table color coded with some formulas for SUM Totals.

 

[mumps]

Make sure that "Sheet1" is the active sheet and try this macro:

Sub AddSheet()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim ws As Worksheet
    For Each c In Range("A2:A" & bottomA)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        On Error GoTo 0
        If ws Is Nothing Then
            Sheets("Sheet2").Select
            Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = c.Value
        End If
    Next c
    Application.ScreenUpdating = True
 End Sub

 

[jnels45]

I got another error becuase my list is as follws : (Dates) -- Is there anyway I can change keep those names or do I need to type in another list of names due to fact that WS names cannot contain "/" ------Even though I typed in the dates as day dash month it reverts back to the 12/2/13 in excel....

Dates</SPAN>

2-Dec</SPAN>

3-Dec</SPAN>

4-Dec</SPAN>

5-Dec</SPAN>

6-Dec</SPAN>

7-Dec</SPAN>

<TBODY>
</TBODY>

And so Forth-What is the best way to c

 

[jnels45]

Other than that - This code perfectly. Thank you for your help.

 

[mumps]

My pleasure. The date will be displayed according to the format you used to format the cells.

 

[Hat_Trick]

Hello all,
My first post here, so please be gentle

I have taken this macro and gotten it to work successfully, but there is one tweak I would like to make.

I would like to have the newly created sheet be placed in the sequence of the list.

So I have the following tabs
List names (the source for the worksheet names)
Template (the worksheet being copied and renamed)
Smith
Jones
Williams

If I insert a name (Stevens) between Smith and Jones, I would like to have the new worksheet get inserted between Smith and Jones.
Smith
Stevens <--
Jones
Williams


The current MACRO drops it at the end of the workbook.
Smith
Jones
Williams
Stevens <--



This is an employee performance measurement file. There are 4 different classifications of employees (each with their own list of names, and their own template) which all roll up to a summary worksheet for each classification. I want to keep worksheets for each classification group together.

Is there a way to do that? Full disclosure, I am very new to this aspect of Excel. I was able to copy the MACRO, paste it into my file, figure out that I needed to change “Sheet2” to the name of my template worksheet. Beyond that, as far as I am concerned, the rest works by PFM (but I am trying to learn).

I have a MACRO set up/customized for each classification group, but they all dump the new worksheet at the end of the line.

I have tried searching for a solution, but could not find anything (or at least could not come up with the magic search string to find it).


Thank you in advance for your help.

 

[mumps]

Hi Hat_Trick. Welcome to the forum. According to forum rules you should not ask a new question in someone else's thread. Please post your question by staring a new thread of your own. Good luck with it.

 

[Hat_Trick]

Hi Hat_Trick. Welcome to the forum. According to forum rules you should not ask a new question in someone else's thread. Please post your question by staring a new thread of your own. Good luck with it.

Sorry, I guess I did not see it as a new question, since it was taking a previous response, and putting a slight twist on it. I literally copied the macro script and inserted it into my file, and tweaked it to the specifics of my file. All I am doing now is looking for a further tweak to that same macro to provide some additional functionality. Through my searches I have seen several other posts which did the same thing...