Previous Topic Next Topic

Auto Generate a copy of a Template Worksheet and rename each

Closed
ChukieD Posts 2 Registration date Friday August 12, 2016 Status Member Last seen August 13, 2016 - Aug 12, 2016 at 07:32 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 15, 2016 at 02:55 AM
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.....

Related:

2 responses

Answer 1 / 2
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Aug 15, 2016 at 02:55 AM
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.
1
Answer 2 / 2
ChukieD Posts 2 Registration date Friday August 12, 2016 Status Member Last seen August 13, 2016
Aug 13, 2016 at 10:48 AM
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
0