add 'name worksheet after cell' macro when creating sheet

[zeegerd]

Hi all,

In Excel (2003) I have this situation: one Summary worksheet, then a range of sheets containing project information.

From the Summary, with a button a macro can be launched to add a new project sheet. This works perfectly. But I would like to name the new sheet after a cell (say C3) on the new sheet dynamically, i.e. if C3 changes, the sheet name should change immediately, preferably. The initial value (when creating the sheet) may simply be 'Project name here'.

I found a macro that can do this (triggered by a change in that cell), but then this macro should be added automatically to that sheet when creating it, right? I didn't succeed in doing so. That one goes like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
On Error Resume Next
Me.Name = Target
End If
End Sub

Any idea? Thanks for your time!

 

[Andrew Poulsom]

Instead of adding a sheet, copy the one that contains the macro you posted.

 

[Tim_K]

as an option
add this code to yr Summary Sheet change event and play with it!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim isect As Range
    Dim n As Integer
    Dim SheetExists As Boolean
    If Target.Cells.Count > 1 Then Exit Sub
    On Error GoTo ResetApplication
    Application.EnableEvents = False
    Set isect = Intersect(Target, Range("A:A"))
    If Not isect Is Nothing And Target <> "" Then
        For n = 1 To Sheets.Count
            If Sheets(n).Name = Target Then
                SheetExists = True
                Exit For
            End If
        Next
        If Not SheetExists Then
            ThisWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = Target
            Me.Activate
            Target.Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
                                       SubAddress:="'" & Target & "'!A1", _
                                       TextToDisplay:=Target.Value
                                     
                    
        End If
    End If
ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.EnableEvents = True
    Set isect = Nothing
End Sub

 

[zeegerd]

Instead of adding a sheet, copy the one that contains the macro you posted.

Hello Andrew,

Thanks, I guess I've tried that but without success. What happens is: the last (filled in) project sheet is copied, a new sheet is created, the last sheet is pasted and the values are emptied.

That way however, the macro for doing the job (if present in the last sheet) is not copied along onto the new project sheet.

Gr, Zeeger.

 

[Andrew Poulsom]

If you copy a worksheet its code is also copied.

 

[zeegerd]

as an option
add this code to yr Summary Sheet change event and play with it!

Hello Tim,

Thanks, that one is interesting but it works a bit differently.
One button/macro refreshes the Summary; another creates a new project sheet.

The other way around is also a reasonable solution, i.e. the sheet name is filled in and the cell shows that (project) name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

But doing a refresh (e.g. F9) yields all project sheets to show the name of the selected project sheet...

I found a different solution, using VB creating a custom function which does the job:
http://www.ozgrid.com/VBA/return-sheet-name.htm

Thanks anyway.

 

[zeegerd]

If you copy a worksheet its code is also copied.

Hi Andrew,

Well, how do you copy a sheet? Perhaps in a different way. In my case (in a macro of course): ctrl+A for the source sheet, ctrl+c, add new sheet, paste on new sheet, empty the necessary cells that will be filled in for the new project. That way, the code in the source sheet is not copied into the new sheet. That should be done automatically, either by 'simply' copying (which you seem to say) or describing in the macro how the sheet-specific macro should be added.

Btw I read that creating 'code with code' (i.e. automatically) may be a tricky thing: http://www.cpearson.com/excel/vbe.aspx

I'm a relative newbie in this field so if this sounds dumb somehow, you know who said it.

 

[Andrew Poulsom]

To copy the active sheet after the last sheet:

ActiveSheet.Copy after:=Worksheets(Worksheets.Count)

 

[GTO]

when you copy all the cells, rather than "add" a new sheet, why not plunk the values into a copy of the "template" sheet that contains (in the sheet's module) code wanted?

 

[zeegerd]

Thanks Andrew, GTO, learned a lot again and I'll go experiment some more!