Worksheet name from list

[dave bates]

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

 

[FormR]

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"

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

 

[dave bates]

thank you very much