Auto Generate and name Worksheets from a list

Solved/Closed

E-Coli - Sep 2, 2008 at 02:33 PM
Gagan - Apr 3, 2017 at 10:47 AM

Hello,

How can I automatically create and name worksheets in a workbook based on a list that exists in another sheet "Summary" in the workbook? The list begins at cell A10. Mind you, this list will vary from workbook to workbook. Do you have some code already compiled to perform this?

E-Coli

Excel auto create tabs from list
Create skype account with gmail - Guide
My contacts list names - Guide
Grand theft auto 1 download - Download - Action and adventure
Close all tabs chrome android - Guide
Create snapchat account - Guide

2 responses

Answer 1 / 2

Ivan-hoe Posts 433 Registration date Saturday February 16, 2008 Status Member Last seen October 17, 2008 110
Sep 6, 2008 at 01:26 AM

Hello E-Coli,
apparently my tips did not help you.
anyway, here is a solution :

Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
    
    Set MyRange = Sheets("Summary").Range("A10")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell
End Sub

siva
Oct 22, 2009 at 01:10 AM

please let me know where exactly script shouldbe done becus I am not familiar with macros but I would like to create sheet name for about a year datewise from the month of NOV 2009 in a single work book or atleast for a period of 3 months

pls reply immdtly

thanks

Glossophobe
Nov 4, 2010 at 11:06 AM

This was beautiful - Than you E-Coli and Ivan-hoe!

Answer 2 / 2

tuta
Mar 25, 2009 at 03:00 PM

Hi

It was really useful your formula but I would need the others worksheets to be equal to the first one. I've trying to do it but I wasn't able to do it. Can you help me one that?

subop
Jul 31, 2013 at 06:13 AM

Sub AutoAddSheet()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets(9).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number 9 in this example
Sheets(Sheets.Count).Name = MyCell.Value 'Renames the new worksheets
Next MyCell
End Sub

ramkyc
Sep 22, 2013 at 07:38 PM

Hi subop and Ivan, Excellent solutions! How do I create a worksheet containing the names of the worksheets in the book? Against each worksheet name, I would like to display the content of cell K2 of each of those worksheets.

TIA
Ramakrishna

RT
Apr 17, 2014 at 12:00 PM

Helo,
I was wondering if it is possible to add a "hyperconnection" (klickable link) between the name in the list and the worksheet whit the same name?

So that when you crate a new worksheet it also adds a connection between the list and the new worksheet. Easyer to navigate between the worksheets, if the list is long, this way.

Hope to get som e help on this topic.

RT

DIL2040
Mar 4, 2015 at 03:08 PM

Good one. I tried this and since my summary tab had 300 records, there were 300 sheets created. Now how do I remove those sheets and restrict it to only 10. I dont want to create a new excel sheet as sooner or later I would need the command for removing the sheet automatically like creating.

Similar discussions

Generate excel workbooks based on excel list

Create multiple copies of excel based on a single template

Search a list of names at one time in excel

macro to compare two excel sheets

How to specify macro for current active sheet

Comparing two excel worksheets

Similar discussions

Subscribe To Our Newsletter!