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

Report
Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013
-
Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020
-
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

2 replies

Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020
428
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
1
Thank you

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

CCM 3488 users have said thank you to us this month

Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013

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
Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020
428
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
>
Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020

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?
Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020
428 > 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
Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013

Thanks so much Trowa!!!! It works perfectly???