Auto Generate workwheet from a list and from a template workshee
Solved/Closed
vera_712
Posts
3
Registration date
Wednesday 29 May 2013
Status
Member
Last seen
5 June 2013
-
30 May 2013 à 00:54
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 10 Nov 2016 à 10:56
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 10 Nov 2016 à 10:56
Related:
- Auto Generate workwheet from a list and from a template workshee
- "You can make a private videos list and share them only with your favorite friends!" - Guide
- Create a playlist out of your liked videos on YouTube - Guide
- Android auto apk - Download - Other
- Counter strike 1.6 cheats list - Guide
- How to stop twitter from auto translating - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
30 May 2013 à 10:52
30 May 2013 à 10:52
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 29 May 2013
Status
Member
Last seen
5 June 2013
30 May 2013 à 19:19
30 May 2013 à 19:19
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
3 Jun 2013 à 10:14
3 Jun 2013 à 10:14
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
9 Nov 2016 à 00:11
9 Nov 2016 à 00:11
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
>
Loco
10 Nov 2016 à 10:56
10 Nov 2016 à 10:56
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 29 May 2013
Status
Member
Last seen
5 June 2013
5 Jun 2013 à 06:21
5 Jun 2013 à 06:21
Thanks so much Trowa!!!! It works perfectly???
