Adding & Naming Worksheets according to a list

traversd · Mar 18th, 2002, 04:33 AM

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 · Mar 18th, 2002, 04:57 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 04:33 AM	#1
traversd Board Regular Join Date: Mar 2002 Posts: 82	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

Mar 18th, 2002, 04:57 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.