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!!!

 

[My Aswer Is This]

We really do not need to be using all 1.5 million cells in row "K" for this.
I can tell the script to use row one to the last row in column K.
These sheet change event scripts can cause you problems.
If someone or some script makes any changes to any value in column "K" the script will run and make you a new sheet.

What would you think about a double click event script. You enter the sheet name in column "K" then double click on the value in column "K" and the script will run. Accidently double clicking a cell would not happen normally.

When we get these answers I will show you how to include both scripts to work. I like solving one thing at a time.
But if you want to keep it at K:K then I will work with that.

 

[My Aswer Is This]

I really like helping people Mark but you keep moving the Goal posts. First we were dealing with column "D" then you changed it to column "K" and now you have changed it to column "J" so let me know when you have a solid answer for me. Combining the two scripts only means inserting a extra "if statement" but I want to settle things one at a time.

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

 

[MarkReddell]

Good evening Robert! Thanks for your interest & reply!!! The first macro creates a separate sheet for all employee's from the "Master Sheet" that then tracks the employee's scheduling, & Attendance Tracking!!! The 2nd macro just run macros from the data Validation drop Down Menu List!!! The two aren't related in any way! However, I need to have access to both codes, combinng them into one code!!! Thanks, Robert!!!

 

[MarkReddell]

Thanks for your question! Since your first code was writen; I had to move some columns in my employee's table!!! My Bad!!! However, The last code works great!!!

 

[MarkReddell]

Back to your other question! My table data for the WWID Number for Employee's is currently: $J$5:$J104. Is their a way to limit the macro to only the current data Dynamicly to table data???l

 

[MarkReddell]

Thanks 4 the help I've received on this issue from: My Aswer Is This!!! I wish I had even 1% of the knowledge of many on this site!!! Thanks again to Mr. Excel & ALL here on this site!!! Goodnight ALL!!! God's Blessings!!!

 

[MarkReddell]

I'm still looking for help on combining these two macro codes!!! Thanks for any help!!!

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

 

[My Aswer Is This]

I'm still a little concerned about what this does.
I asked you once but you did not answer my question.

Application.Run Target.Value

I know it runs some sort of script

Or does the drop down have a list of macro names and you choose which one to run

 

[MarkReddell]

Yes! Thanks!!! The Drop down menu list runs macros in $G$1 !!!

 

[My Aswer Is This]

OK:
Try this:

This script works in the range ("J5:J Lastrow)

Meaning it works from J5 to the last used range in column J

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "J").End(xlUp).Row + 1
If Not Intersect(Target, Range("J5:J" & Lastrow)) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Sheets("Master").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
Sheets("Employee's").Activate
End If

If Target.Address <> "$G$1" Then Exit Sub
Application.Run Target.Value
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