Create worksheets from a list using excel VBA

Microsoft Office excel 2007 - upgrade
December 20, 2010 at 06:51:39
Specs: Windows 7
I have a list of names and want to create a spereadsheet for each in a single workbook. the first spreadsheet will contain the full list of names, second spreadsheet will have a template of how I want all each spreadsheet to be created for each name on the list. Each name contained in the list in spreadsheet 1 should hyperlink to its corresponding spreadsheet (tab) contained in the workbook. Do you know is there VBA job for this?

See More: Create worksheets from a list using excel VBA

December 20, 2010 at 16:57:56
I don't know what the list in your first sheet looks like, so I'll assume your list of names begins in A1.

All I did was take the macro that you asked for when you wanted to create the TOC and added some code to copy the second sheet over and over, naming each copy based on the list in Sheet 1.

Once all the sheets have been created, the TOC code creates a TOC based on those sheets. There are some subtle changes to the original code, but it more or less does the same thing it did before.

BTW...if this is what you wanted in the first place, you should have asked for it. When you ask for code in "pieces" we end up having to do more work than if all the requirements are set forth right up front. Please keep that in mind the next time you need some help.

As always, test this code in a backup copy of your workbook.

Option Explicit
Sub AddSheetsAndTOC()
   Dim ws As Worksheet, shtName As String, cShade As Long
   Dim nRow As Long, i As Long, N As Long, x As Long
   Dim lastRw As Long, nxtName As Long, lastSht As Long

     'Turn off events and screen flickering.
     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
            With Sheets(1)
              lastRw = .Range("A" & Rows.Count).End(xlUp).Row
                For nxtName = 1 To lastRw
                  lastSht = Sheets.Count
                    Sheets(2).Copy After:=Sheets(lastSht)
                    ActiveSheet.Name = .Range("A" & nxtName)
            End With
    nRow = 4: x = 0
     'Check if sheet exists already; direct where to go if not.
    On Error GoTo hasSheet
     'Confirm the desire to overwrite sheet if it exists already.
    If MsgBox("You already have a Table of Contents page." _
    & vbLf & vbLf & _
    "Would you like to overwrite it?", _
    vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
    Exit Sub
    x = 1
     'Add sheet as the first sheet in the workbook.
    Sheets.Add before:=Sheets(1)
    GoTo hasNew
    GoTo hasSheet
     'Reset error statment/redirects
    On Error GoTo 0
     'Set a little formatting for the TOC sheet.
    ActiveSheet.Name = "TOC"
    With Sheets("TOC")
        .Cells.Interior.ColorIndex = cShade
        .Rows("4:65536").RowHeight = 16
        .Range("A1").Value = "Designed by VBAX"
        .Range("A1").Font.Bold = False
        .Range("A1").Font.Italic = True
        .Range("A1").Font.Name = "Arial"
        .Range("A1").Font.Size = "8"
        .Range("A2").Value = "Table of Contents"
        .Range("A2").Font.Bold = True
        .Range("A2").Font.Name = "Arial"
        .Range("A2").Font.Size = "24"
    End With
     'Set variables for loop/iterations
    N = ActiveWorkbook.Sheets.Count
    For i = 4 To N
        With Sheets("TOC")
                shtName = Sheets(i).Name
                 'Add a hyperlink to A1 of each sheet.
                .Range("C" & nRow).Hyperlinks.Add _
                   Anchor:=.Range("C" & nRow), Address:="#'" & _
                   shtName & "'!A1", TextToDisplay:=shtName
                .Range("C" & nRow).HorizontalAlignment = xlLeft
            .Range("B" & nRow).Value = nRow - 1
            nRow = nRow + 1
        End With
    Next i
     'Perform some last minute formatting.
    With Sheets("TOC")
    End With
     'Turn events back on.
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Complete!", vbInformation, "Complete!"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

December 21, 2010 at 04:19:39
Thanks again for your help DerbyDad03. That worked a treat once I figured out that each cell in the column under A1 could not be more than 31 characters.
Apologies, I had no idea when posteing my origional request that i would be requiring additional help later on. Working on two seperate jobs right now. Will definately bear in mind for future requests.

Thanks again, really appreciate your help.

Report •

January 6, 2011 at 05:25:06
Hi Derby Dad, Is it possible to make it skip the ones which are already created? thus making it possible to create new entries in the list. Result - macro will skip the names which already have a sheet and continue with the new ones until last row


Report •
Related Solutions

Ask Question