Adding & Naming Worksheets according to a list

[traversd]

Hi, I have a list of Customers on a "Data" worksheet. I want to make Excel add a new worksheet for each customer entry and name the worksheet to match the customer name, when that is done I want to copy the data and format of the "Main" worksheet to each consecutive worksheet

Any help would be appreciated...

Thanks
Darren

 

[Dave Hawley]

Hi Darrren

Try this:


Sub DoIt()
Dim rCell As Range
Dim strSheetName As String

Application.ScreenUpdating = False
Sheets("FormatSheet").UsedRange.Copy

For Each rCell In Range("A1", Range("A65536").End(xlUp))
strSheetName = rCell
Sheets.Add().Name = strSheetName
Sheets(strSheetName).Range("A1").PasteSpecial xlFormats
Next rCell

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Change then sheet name "FormatSheet" to suit and Range("A1", Range("A65536").End(xlUp)) to the range with your data. Also ensure there are no blank rows or ilegal sheet names between your data.