Last Sheet Name - VBA

[shemayisroel]

Hi,

I found this code below that extracts the last sheet name. I need the code modified that it auto updates when a sheet is inserted as now you require to go to teh formula and refresh it in order to update the Last Sheet Name. Is it possible to build it in the code?

http://www.cpearson.com/excel/excelm.htm

Public Function LastSheetName()
LastSheetName = Sheets(Sheets.Count).Name
End Function

 

[Marcelo Branco]

Hi,

Maybe this

Paste the code below in ThisWorkbook code-page

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim shName As String
    
    shName = LastSheetName
    MsgBox shName

End Sub

M.

 

[shemayisroel]

Hi,

Maybe this

Paste the code below in ThisWorkbook code-page

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim shName As String
 
    shName = LastSheetName
    MsgBox shName
 
End Sub

M.

Thanks, got a #NAME error when putting =LastSheetName() in the formula bar.

What am I doing wring?

Pasted the code in "ThisWorkBook"

 

[Marcelo Branco]

Thanks, got a #NAME error when putting =LastSheetName() in the formula bar.

What am I doing wring?

Pasted the code in "ThisWorkBook"

It worked for me.

Did you paste the LastSheetName function in a standard module?

M.

 

[mikerickson]

The problem is that adding a sheet does not trigger a calculate event.
This will fix that

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Calculate
End Sub

 

[GTO]

Not sure which code you pasted in ThisWorkbook. Marcelo's code needs to be in ThisWorkbook.

But...

Public Function LastSheetName()
LastSheetName = Sheets(Sheets.Count).Name
End Function

...needs to be in a Standard Module.

 

[Marcelo Branco]

If you are putting the last sheet name in, say, A1 of Sheet1 (adjust to suit), try

1. Copy/Paste the function LastSheetName in a Standard Module

Public Function LastSheetName() As String
    LastSheetName = Sheets(Sheets.Count).Name
End Function

2. Put this code in ThisWorkbook code-page

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    
    Sheets("Sheet1").Range("A1") = LastSheetName
    
End Sub

 

[shemayisroel]

Thank you to

Marcelo Branco,

GTO and

mikerickson

... for your help. I've now resolved it.

Very greatful for your help and swift response to my questions.

 

[Marcelo Branco]

You are welcome!

Glad to help

M.

 

[FishingIsMoreFun]

In my version of Excel the Last sheet name does not change after adding a sheet.

Unless you mean the name of the most recently added sheet.

But the code I see always grabs the last sheet name, which never changes...