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]

You said:

"Upon an entry I need a sheet to be created"

Do you mean anytime you enter a value into column "D" you want this sheet created automatically?
Or do you mean you want all these sheets created when you run the macro?

And are you sure the sheet is named "Mater Sheet"

Or do you mean sheet is named "Master"

Put the sheet name in quotes:

 

[MarkReddell]

You said:

"Upon an entry I need a sheet to be created"

Do you mean anytime you enter a value into column "D" you want this sheet created automatically?
Or do you mean you want all these sheets created when you run the macro?

And are you sure the sheet is named "Mater Sheet"

Or do you mean sheet is named "Master"

Put the sheet name in quotes:

Yes! I need anytime a make an entry that the macro creates a ws in the emp. id # as the new ws. And, u r correct the sheet is: Master Sheet.

 

[My Aswer Is This]

So why do we need to use rows 4 to 103

If you want the sheet created immediately after you enter a value into column "D" why do we need to restrict this to rows 4 to 103

And you said:
And, u r correct the sheet is: Master Sheet.

I said put the sheet name in quotes.

Is the sheet named "Master" or "Master Sheet"

And having this done immediately after you enter a value in column "D" may be dangerous. If you enter "Dog" into Column "D" a sheet named "Dog" will automatically be made.

 

[My Aswer Is This]

Ok.
Here is what you asked for:
When ever you enter any value into column "D" in the Range("D4:D103")
The sheet named "Master" will be copied and named what ever value you entered into column "D"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet Named "Employee's tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4:D103")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("Master").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
End If
End Sub

This will not create a whole list of new sheets like you said here:
"Within this sheet in Col. D4:d103 is a list of employee ID number"

This script will only create a new sheet when you manually enter a value in column "D"

That's why I asked if you wanted this to happen when you run the script or happen automatically when you enter a id# and you said I want this to happen automatically when you enter a value.

 

[MarkReddell]

Ok.
Here is what you asked for:
When ever you enter any value into column "D" in the Range("D4:D103")
The sheet named "Master" will be copied and named what ever value you entered into column "D"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet Named "Employee's tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4:D103")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("Master").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
End If
End Sub

This will not create a whole list of new sheets like you said here:
"Within this sheet in Col. D4:d103 is a list of employee ID number"

This script will only create a new sheet when you manually enter a value in column "D"

That's why I asked if you wanted this to happen when you run the script or happen automatically when you enter a id# and you said I want this to happen automatically when you enter a value.

Thank You very Much! This worx perfectly!!! Have a Blessed Day!!!

 

[My Aswer Is This]

Glad to hear it works for you.
I added a couple lines of code to warn you if that sheet has already been made.
Use this script

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4:D103")) 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
Exit Sub
M:
MsgBox "That sheet allready exist"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Employee's").Activate
End Sub

 

[MarkReddell]

Glad to hear it works for you.
I added a couple lines of code to warn you if that sheet has already been made.
Use this script

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4:D103")) 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
Exit Sub
M:
MsgBox "That sheet allready exist"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Employee's").Activate
End Sub

How would you add this code to your macro???

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

Thanx 4 your help!!!

 

[My Aswer Is This]

So you only want the script to run if a change is made to range ("F1") is that what you want?

I'm not sure what this line of code does:
"Application.Run Target.Value"

How would you add this code to your macro???

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

Thanx 4 your help!!!

 

[My Aswer Is This]

If the answer to my question is yes.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) 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
Exit Sub
M:
MsgBox "That sheet allready exist"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Employee's").Activate
End Sub