VBA: Creating new Sheets based on copy, Nameing them the Cells

Zodiacswe - Jan 21, 2016 at 10:16 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jan 22, 2016 at 05:03 AM
Ok, i am gonna try to be so clear i can.
on Sheet 1 (Start Sida) i have a list from cells A9 to A500 with names. atm only A9-A52 have names in them.
I want when i typ in a new name in cell A53 and forward, that Excel creates a new Sheet with the name i typed in cell A53 and the new sheet should be a copy of Sheet 7 (themplate)

So i typ in "Company" new Sheet with the name Company and a copy of Sheet 7 shall appear.

can anybody help me with what i should use for VBA code for this?

1 response

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jan 22, 2016 at 05:03 AM
Hello Zodiacswe,

Without seeing your work book, I'm doing some guessing here, but I think that the following code may do the task for you:-

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim wsStart Sida As Worksheet
   Dim wsNew As Worksheet
   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
   If Intersect(Target, Range("A9:A" & Rows.Count)) Is Nothing Then Exit Sub
On Error Resume Next

Set wsNew = ThisWorkbook.Sheets(Target.Value)

'This next part of the code will check to see if a sheet with the same name 'exists. If it does, a message box will pop up warning you of this.

   If Not wsNew Is Nothing Then
   MsgBox "A sheet for name " & Target.Value & " already exists. No new sheet will be added.", vbExclamation
   Exit Sub
End If

Set wsStart Sida = ActiveSheet

Sheets("Template").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)                                                                      
   With Sheets(ThisWorkbook.Sheets.Count)
   .Name = Target.Value
End With

MsgBox "A new sheet is ready!", vbExclamation
Sheets("Start Sida").Select

End Sub

To implement the code, right click on the active sheet (Start Sida), select "view code" and in the big white field that appears, paste the above code.
Now, every time that you enter a new name in Column A in "Start Sida", click away and the code will activate creating a new named sheet for you.
You will need to name your Sheet 7 as Template.

I feel that an error will arise, so it would be best if you could upload a sample of your work book (be careful with any sensitive data) so that we can see exactly what you would like to do. You can upload a sample by using a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post back here the link to your sample file.

I hope that this helps.