CREATE NEW WORKBOOK AND SHEETS BASED ON COLUMN DATA

[control freak]

Hello, this is what i need to accomplish:

I have a workbook with a sheet named "template" and another sheet where

user can enter names in column A.


When button is pressed after all names entered, i want the button to
create a new workbook complete with multiple (however many names
entered in column A) copies of the template sheet named after contents
of column A in original workbook. as well as name the new workbook
from the contents of a cell in original workbook.


I have gotten as far as creating new workbook with only 1 SHEET copy,
hangs
after first copy, my guess is that its focus is now on new workbook and

cannot complete the macro.


my code so far (with appreciated help from forum) is:


Private Sub CommandButton1_Click()
With Worksheets("START")
For Each cell In .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not IsEmpty(cell) Then
Worksheets("template").Copy
ActiveSheet.Name = cell.Value
End If
Next
End With
End Sub


I hope someone has some insight if this can be done??


Thanks again


Troy


Reply »


From: Muhammed Rafeek M - view profile
Date: Thurs, Jul 20 2006 1:17 am
Email: Muhammed Rafeek M <[email protected]>
Groups: microsoft.public.excel.misc
Not yet ratedRating:
show options

Pls try this one:

Private Sub CommandButton1_Click()
With Worksheets("START")
For Each cell In .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not IsEmpty(cell) Then
Worksheets("template").Copy
ActiveSheet.Name = cell.Value
.Activate
End If
Next
End With
End Sub

From: control freak
Date: Thurs, Jul 20 2006 8:13 am

Thank you for your reply, this code will create multiple workbooks, I
need it to create only 1 workbook with multiple copys of the sheet
"template" based on the names listed in column a.

So if i have 3 names( ted, bruce, art) listed in column A, i need to
create 1 new workbook with 3 sheets in it named (ted, bruce, and art)
that are copies of the original sheet "template".


and if possible name the new workbook based on a cell on original
workbook (say column b for example).


I appreciate all the help from these forums as I am not familiar with
VBA coding.


Troy

 

[Hayeso]

Option Explicit
Private wbSource As Workbook, wbDestination As Workbook
Private shtTemplate As Worksheet, shtStart As Worksheet, shtDest As Worksheet


Public Sub CreateNewWb()
Dim Names As Range, Name

Set wbSource = ThisWorkbook
With wbSource
Set shtTemplate = .Sheets("Template")
Set shtStart = .Sheets("START")
End With
Set wbDestination = Workbooks.Add
'Here we Name the Workbook with the contents of cell B2 of the START sheet
wbDestination.SaveAs (shtStart.Cells(2, 2))

' Define the list of names from column A of START sheet excluding the
heading in cell A1
Set Names = shtStart.Cells(2, 1)
With Names
Set Names = .Resize(.CurrentRegion.Rows.Count - 1, 1)
End With
For Each Name In Names
shtTemplate.Copy
after:=wbDestination.Sheets(wbDestination.Sheets.Count)
shtTemplate.Name = Name
Next
End Sub

 

[control freak]

Thank you so much, i almost have this working, just one little glitch i
hope you can help me with...

ex. of column a (art, ted, fred)
ex. of column b (client)

when i run this code i get a new workbook named 'client' with sheets
named (template, art, ted) and on my original workbook my template
sheet is renamed to (fred), so i just need to get that fred sheet over
to new workbook and dont rename my original template sheet (hope this
is clear)

Again thank you so much for your reply it was very helpful. other than
that little glitch it works exactly as i had hoped (except i have extra
sheet 1, sheet 2 and sheet 3 in the new workbook, must be default of
creating workbook)...;-)

Troy