Worksheet name from list
Results 1 to 3 of 3

Thread: Worksheet name from list
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Location
    Derbyshire England
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Worksheet name from list

    Hi, help please with creating worksheets in a workbook

    Im using Excel 2016

    I have a VB to create new worksheets based on a list of names on a summary sheet but I would like the new worksheets to be to set layout including column widths, formats and formula. Is the easiest way to do this to extend the VBA to do a copy and paste special to all of the sheets when created.

    The VBA that I'm using to create the worksheets is

    Sub AddNamedSheets()
    Dim i&
    With sheets ("Sheet1")
    For i = 52 To 2 Step-1
    sheets.Add.Name=.Cells(i,"A").Value
    Next i
    End With
    End Sub

    thanks

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,062
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Worksheet name from list

    Hi, how about copying a "master" sheet that already has the layout, formulas etc rather than creating new sheets and back applying.

    Something like this, where, for example, the name of the pre-formatted sheet is "master"

    Code:
    Sub AddNamedSheets()
    Dim i&
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        For i = 52 To 2 Step -1
            Sheets("master").Copy Before:=Sheets(1)
            ActiveSheet.Name = .Cells(i, "A").Value
        Next i
    End With
    End Sub
    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Location
    Derbyshire England
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet name from list

    thank you very much

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •