Auto Generate a copy of a Template Worksheet and rename each Closed

Question

Hello, 
I am very new to Excel and VB and am trying to create a macro that will do the follow;

1. Create a copy of a template worksheet (named Template) for each name listed in column A (begins in at A1) of a Worksheet named Summary. 
2. After the worksheets are created, rename each worksheet with the list of names contained in column A (begins in at A1) of the same worksheet (Summary). and...
3. The name on the tab should be then be entered in to cell A1 of each worksheet.

The expected result:
If column A contains 10 names, then would 10 copies of the Template worksheet should be creatred, with each worksheet renamed to match each of the names and each worksheet containing the worksheets name in cell A1. I would expect this to happen if there were 1 or 100 names listed in column A of the Summary worksheet.

Any Help is very, very much appreciated... Thank you.....

Configuration: Windows / Chrome 52.0.2743.116

ChukieD · Answer

Just realized that there is a 31 character limitation in the amount of characters that can be used for the worksheet name. So need to specify that A1 must contain the full name and the tab for the worksheet could contain a part of name in A1.

Thanks

vcoolio · Answer

Hello ChukieD,

I'm not overly sure how you have set up your work book but below is a code that may, at least, set you in the right direction:-

Option Explicit

Sub CreateSheets()

Dim wsSumm As Worksheet, wsTmp As Worksheet
    Dim shtNames As Range, N As Range

With ThisWorkbook
    Set wsTmp = .Sheets("Template")
    Set wsSumm = .Sheets("Summary")
    Set shtNames = wsSumm.Range("B2:B" & Rows.Count).SpecialCells(xlConstants)
    
Application.ScreenUpdating = False

For Each N In shtNames
        If Not Evaluate("ISREF('" & CStr(N.Text) & "'!A1)") Then
          wsTmp.Copy After:=.Sheets(.Sheets.Count)
            ActiveSheet.Name = CStr(N.Text)
              ActiveSheet.Range("A1").Value = N.Offset(, -1).Value
              End If
         Next N
wsSumm.Select
End With

Application.ScreenUpdating = True

End Sub

Following is the link to my test work book:-

https://www.dropbox.com/s/vkwf8tr0amfqjm2/ChukieD%28create%20sheets%20from%20template%29.xlsm?dl=0

You'll see in the sample that there is a Summary sheet with the names and a set of columns where data can be placed.

You will also see a Template sheet. This is what the code uses to create a sheet for each and every name that is entered into the Summary sheet meaning that each sheet will be set out the same.

In your second post, you stated that the names in Column A will exceed the 31 character limit for sheet tabs, so, in the Summary sheet, I have created an ID column (Column B). Hence each name will have a shortened ID with which each sheet will be named. Cell A1 in each created sheet will have the full name in it (from Column A). You can experiment with the names and IDs to suit yourself in the test work book.

Click on the "RUN" button to see it work.

A template sheet may not be necessary in your case as individual sheets can be created from the Summary sheet instead, assuming that you would like all sheets to be set out the same as the Summary sheet. Let us know your thoughts.

I hope that this helps.

Cheerio,
vcoolio.

Auto Generate a copy of a Template Worksheet and rename each

2 responses

Subscribe To Our Newsletter!