Auto Generate workwheet from a list and from a template workshee [Solved/Closed]

Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013
- - Latest reply: TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
- Nov 10, 2016 at 10:56 AM
Hello All,

At the moment, I found out how to auto generate worksheet from a list. However, the generated ones are blank worksheets.

May I ask how I can specify a template worksheet to be auto generated on?

Many thanks!

Vera
See more 

2 replies

Best answer
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
1
Thank you
Hi Vera,

Instead of creating a new sheet, you could copy an existing (hidden) one.

If you need further assistance post your code along with your question.

Best regards,
Trowa

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 5735 users have said thank you to us this month

Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013
0
Thank you
Thanks so much Trowa! Now it works!

May I ask one more thing? I got the list in Sheet1 with two columns as below?

Is it possbile to copy each Emp Code to Cell $C$7 in the corresponding worksheet (those automatically generated ones from the "Template" worksheet) as well?

Many thanks!

Emp Code Employee Name
90912 A
90913 B
90914 C
90915 D


My current workings are below:


Sub Create_WS()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet1").Range("B2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
Next MyCell
End Sub


Vera
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Sure thing Vera,

I added a line to the code:
Sub Create_WS()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet1").Range("B2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = MyCell.Value
    Sheets(MyCell.Value).Range("C7").Value = MyCell.Offset(0, -1).Value
Next MyCell

End Sub

Best regards,
Trowa
> TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
-
Hi TrowaD,

what if you add inputs to list? there's error message about name being already used.
any suggestions how to make it work with next items added to the list without deleting previous ones?
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 > Loco -
Hi Loco,

For that we need to determine if a sheet already exist and to that I like to use a custom fuction.

Check out this code:
Sub Create_WS()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet1").Range("B2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
    If Not SheetExists(MyCell.Value) Then
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        Sheets(MyCell.Value).Range("C7").Value = MyCell.Offset(0, -1).Value
    End If
Next MyCell

End Sub

Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet

If Len(Sheets(SheetName).Name) > 0 Then
    SheetExists = True
    Exit Function
End If

NoSuchSheet:
End Function


Best regards,
Trowa
vera_712
Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013
-
Thanks so much Trowa!!!! It works perfectly???