Creating a New Worksheet within the same Workbook based on the data within a cell from a table

[Vonsteiner]

Hello,

I am having difficulty getting the syntax right for this VBA code I am working on. Basically I have a table where the sales person will add new rows as they begin a new action. I am trying to have Excel create a new worksheet based off a template and name the new worksheet with the cell value. The table name is MasterActionList. The table column is column 4 or "Action Name". I have a worksheet within the workbook named "Template". I have tried the code with the range set to the table column and as it is with just using the column range.

Private Sub CreateAndNameWorksheets()

    Dim c As Range
    Dim ac As Range
    
    Application.ScreenUpdating = False
    
    'ac = ActiveSheet.ListObjects("MasterActionList").ListColumns(4).Range.Select
    
    Sheets("All Actions").Select
    
    For Each c In Range("D2:D201")
        c.Select
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = c.Value
        Sheets("Template").Cells.Copy
        ActiveSheet.Paste
        Range("A1").Select
        Application.CutCopyMode = False
        Sheets("All Actions").Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=c.Value & "!A1", TextToDisplay:=c.Value
        
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

Any help would be most appreciated. Thank you.

 

[FatBoyClam]

What is the code doing?

What are you expecting it to do?

What kind of values are there in column D?
There are rules about what you can name sheets, so if it's erroring on the line to rename the sheet, it may be the name you're trying to give it is not valid, or maybe it's not unique.

 

[Vonsteiner]

The code isn't doing anything at all. It doesn't create a new worksheet and it doesn't produce any errors that I have seen.

The code is supposed to be creating a new worksheet based on what is input in cell D. The data in cell D will just be regular words and maybe a number or two. No special characters or anything weird.

So the sales person enters the name of their project into cell D. I want it then to create a worksheet with the same name as what they entered into cell D. The new worksheet will be a copy of the 'Template' worksheet already within the workbook. I would prefer to make it dynamic so it creates the sheet when a new row is added to the table basically.

 

[FatBoyClam]

How are you triggering the code after the user has entered a value?

 

[Vonsteiner]

That is a good question. I'm not really sure. I guess I thought it would just happen, but now that you mention it I'm not. Would there be a way to trigger the code to make the new sheet but stay on the current sheet?

 

[FatBoyClam]

yes, there are a few options.

I think the one you want is when the user has entered the values for the new row.

So, in the code module for the sheet, you can use the Worksheet_Change event. This triggers whenever any cell value on the worksheet is changed directly (note: not if the cell has a formula that changes displayed value based on external factors)

In this case we'll work with the table, as you have one, and it makes things a lot easier.

First we'll check that the changed cell is the lastrow of column 4 of the table "MasterActionList", to do this we declare a couple of objects, one is the table, the next is the column, then we set the range object "c" to be the last cell in the column, finally we confirm that c and the changed cell (Target) intersect

Having done all that, we run your code (with a couple of minor tweaks to ensure we're referencing ranges on the correct worksheet, and to accomodate values with space in)

Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range
    Dim ac As Range
    Dim tbl As ListObject
    Dim lCol As ListColumn
   
   
    Application.ScreenUpdating = False
   
    Set tbl = ActiveSheet.ListObjects("MasterActionList")
   
    Set lCol = tbl.ListColumns(4)
   
    Set c = lCol.DataBodyRange.Cells(lCol.DataBodyRange.Rows.Count, 1)
   
    If Not Application.Intersect(Target, c) Is Nothing And Target.CountLarge = 1 Then
   
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = c.Value
        Sheets("Template").Cells.Copy
        ActiveSheet.Paste
        ActiveSheet.Range("A1").Select
        Application.CutCopyMode = False
        Target.Worksheet.Activate
        ActiveSheet.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:= "'" & c.Value & "'!A1", TextToDisplay:=c.Value
       
    End If
   
    Application.ScreenUpdating = True


End Sub

this doesn't trigger when a new row is added (a new row is added when a value is entered into any cell below the current last row, it seems likely that the user will enter the value for the column in question 4th, as it's the 4th column in the table). It triggers when any value in the worksheet is changed - this is why we have to test to see if the cell changed is one we're interested in.

If the user changes an existing value in the last row of the table, the code will trigger, pass the test and try to create a new sheet with the new value.

This will happen even if the new value is the same as the old value (user overtypes the value with the current value, or they start to edit the cell, then press tab or enter without making a change). In this event the code will fail, as it'll try to create a sheet with a name that already exists.


Your unhandled risk is still the user inputting a value that is not a valid tab name in excel, such as;

1) Something more than 31 characters in length
2) Something the same as an existing tab name
3) something containing any of these characters: \ , / , * , ? , : , [ , ]

You cannot rely on users to not break the rules. you need to consider how you're going to handle it when they do.

Thus what you want might not be what you need,

 

[Vonsteiner]

Yeah, going through all this is making we re-think the process. I will check out how this works to see if it will work with how everyone needs it to work. Thank you very much for all you patience and help.

 

[Vonsteiner]

This actually works perfectly. Is there a way to have the new sheet put the new worksheet name in Cell A1?

 

[FatBoyClam]

Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range
    Dim ac As Range
    Dim tbl As ListObject
    Dim lCol As ListColumn
   
   
    Application.ScreenUpdating = False
   
    Set tbl = ActiveSheet.ListObjects("MasterActionList")
   
    Set lCol = tbl.ListColumns(4)
   
    Set c = lCol.DataBodyRange.Cells(lCol.DataBodyRange.Rows.Count, 1)
   
    If Not Application.Intersect(Target, c) Is Nothing And Target.CountLarge = 1 Then
   
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = c.Value
        Sheets("Template").Cells.Copy
        ActiveSheet.Paste
        ActiveSheet.Range("A1").Select
        ActiveSheet.Range("A1").Value = c.Value
        Application.CutCopyMode = False
        Target.Worksheet.Activate
        ActiveSheet.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:= "'" & c.Value & "'!A1", TextToDisplay:=c.Value
       
    End If
   
    Application.ScreenUpdating = True


End Sub

That should do the trick.

 

[Vonsteiner]

Thanks a ton FatBoyClam. I was trying to input an actual formula into the cell (ActiveCell.FormulaR1C1 = =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)), which of course wasn't working. In your original post you mention the code will not work if the user uses the same name or a non-valid name. I noticed the code also won't work if they delete the contents of the cell. Well, it works to an extent, as in it breaks but creates a new blank worksheet. Is there a way to let the user know if they enter a duplicate name or invalid name so they can correct without the code breaking and a way to stop the code when they delete the contents of the cell so that it doesn't create a blank worksheet or break the code? I really appreciate all your help. Thank you.