Auto Generate workwheet from a list and from a template workshee
Solved/Closed
vera_712
Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013
-
May 30, 2013 at 12:54 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 10, 2016 at 10:56 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 10, 2016 at 10:56 AM
Related:
- Auto Generate workwheet from a list and from a template workshee
- How to stop facebook from auto refreshing - Guide
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Grand theft auto iv download apk for pc - Download - Action and adventure
- Counter strike 1.6 cheats list - Guide
- All contact number list with name - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
May 30, 2013 at 10:52 AM
May 30, 2013 at 10:52 AM
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
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
vera_712
Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
June 5, 2013
May 30, 2013 at 07:19 PM
May 30, 2013 at 07:19 PM
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
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
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jun 3, 2013 at 10:14 AM
Jun 3, 2013 at 10:14 AM
Sure thing Vera,
I added a line to the code:
Best regards,
Trowa
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
Loco
>
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
Nov 9, 2016 at 12:11 AM
Nov 9, 2016 at 12:11 AM
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?
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
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
>
Loco
Nov 10, 2016 at 10:56 AM
Nov 10, 2016 at 10:56 AM
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:
Best regards,
Trowa
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
Jun 5, 2013 at 06:21 AM
Jun 5, 2013 at 06:21 AM
Thanks so much Trowa!!!! It works perfectly???
