Macro to Create Worksheets from a List

[MarkReddell]

Hello Excel guru's!!! I need help in writing a macro to create a worksheet based on an entry in sheet named: Employee's. Within this sheet in Col. D4:d103 is a list of employee ID number. Upon an entry I need a sheet to be created with the employee ID number by copying the sheet named: Mater Sheet. Is this even possible??? Help Please!!!

 

[MarkReddell]

Yes!!! I have about 5 macros that I run often from the Employee's worksheet. So, I need to be able to run those plus your macro also!!!

 

[My Aswer Is This]

So my script in post # 10 should work. You will need to enter sheet name in "F1" . Let me know if this works.

Yes!!! I have about 5 macros that I run often from the Employee's worksheet. So, I need to be able to run those plus your macro also!!!

 

[MarkReddell]

Yes! I need to be able to run a macro from $G$1 from a Drop down macro Menu List, & also be able to run your code as well! I couldn't get both to work with your last code! Can this be done??? When I try to run this code with both of these together on the Emploee's sheet; niether of these macros will run without an error!!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Sheets("Master Sheet").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
Sheets("Employee's").Activate
End If
Exit Sub
M:
MsgBox "That WWID already exist!!! This Worksheet Duplication worksheet will be deleted! Please delete or correct this entry on this Employee's worksheet!"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Employee's").Activate
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$5" Then Exit Sub
Application.Run Target.Value
End Sub

 

[My Aswer Is This]

I'm getting confuse now. Are you saying the script in post # 10 is not working. To get it to work you have to enter your sheet name into Range "F1" only. I thought that was what you wanted. If that's not what you want you have to explain more in detail what you want changed.

Yes! I need to be able to run a macro from $G$1 from a Drop down macro Menu List, & also be able to run your code as well! I couldn't get both to work with your last code! Can this be done???

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Sheets("Master Sheet").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
Sheets("Employee's").Activate
End If
Exit Sub
M:
MsgBox "That WWID already exist!!! This Worksheet Duplication worksheet will be deleted! Please delete or correct this entry on this Employee's worksheet!"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Employee's").Activate
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$5" Then Exit Sub
Application.Run Target.Value
End Sub

 

[MarkReddell]

I need to get both macros to work together from the Employee's Code page, however when I try place them both together neither of them will run!!! That 2nd macro code just runs a macro from my drop down list in G1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Sheets("Master Sheet").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
Sheets("Employee's").Activate
End If
Exit Sub
M:
MsgBox "That WWID already exist!!! This Worksheet Duplication worksheet will be deleted! Please delete or correct this entry on this Employee's worksheet!"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Employee's").Activate
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$1" Then Exit Sub
Application.Run Target.Value
End Sub

 

[My Aswer Is This]

See what your doing is changing around my script and maybe you do not understand what your changing.

You made this change:

"If Not Intersect(Target, Range("K:K")) Is Nothing Then"

This means any time any value in column "K" is changed the script will run.

Why do you need this?

Originally you said "F1"

But you change the script to K:K which means the entire column K

 

[MarkReddell]

I had to change those col. entry to inc. the whole col K just in case of expanding table entries. The top code that modified worx prepectly! However I still need get the other macro to work with yours also!!! I really want to thank you very much for ALL your effort!!!

 

[My Aswer Is This]

If you want to run both scripts in the same sheet I can fix it.

But you have to tell me what cell do you want to use to run the script which creates new sheets.

And then I can combine both scripts.

 

[Trebor76]

Hi Mark,

Can I ask why you want to do this as having a separate tab that has the same layout for every employee, in my humble opinion, is not the way to go. Having the Master tab with a drop-down of employee names that the rest of the tab uses formulas to return the data is a far better more efficient option.

Robert

 

[MarkReddell]

I guess that combining the two codes would work!!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J:J")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Sheets("Master Sheet").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
Sheets("Employee's").Activate
End If
Exit Sub
M:
MsgBox "That WWID already exist!!! This Worksheet Duplication worksheet will be deleted! Please delete or correct this entry on this Employee's worksheet!"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Employee's").Activate
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$1" Then Exit Sub
Application.Run Target.Value
End Sub

Add just these two lines of code to the first code would be what I need!!! Thanks!!!

If Target.Address <> "$G$1" Then Exit Sub
Application.Run Target.Value