Refresh funtion on worksheet

Question

I have a function that gathers the name of sheet 4

Function LastSheetName()

Application.Volatile True
LastSheetName = Worksheets(4).Name

End Function

Then I want to output this name within a cell on my worksheet

=LastSheetName()

This works as intended.

However, sheet 4 will be deleted, added and will have a different name each time.

Now the formula on my sheet doesn't update when a new sheet 4 is added like I want it to. It stays as #VALUE!, as obviously it was looking at the old sheet 4, then the sheet was deleted, before the new sheet 4 was in place.

I need something to add at the end of my code for the "Add Sheet" button that refreshes this formula =LastSheetName() after it has added the new sheet 4.

I have tried adding application.Volatile to the beginning of my function, like above, with no success.

I have tried adding

Worksheets(4).EnableCalculation = False
Worksheets(4).EnableCalculation = True

To the end of my code, no success either.

EDITL Sheet 4 not 6 sorry

What has `Worksheets(6)` got to do with what you call `sheet 4` ? It's not clear what you are talking about. — Robin Mackenzie, Apr 20 '17 at 11:57
Perhaps [this answer](http://stackoverflow.com/questions/27169070/identifying-a-worksheet-other-than-by-its-name/27169154#27169154) and the references therein are helpful — Ioannis, Apr 20 '17 at 11:57
The function name looks like you're looking for the name of the last sheet rather than `sheet 4` or the 6th sheet in your workbook. With that in mind maybe try `LastSheetName = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name` - this won't include chart sheets. Use just `Sheets` instead of `Worksheets` to include a chart sheet. — Darren Bartrup-Cook, Apr 20 '17 at 12:04
Another way would be to clear the contents of the cell on `Sheet 4` and then copy the new data into it. You won't have to delete the sheet and your reference won't be lost. — Darren Bartrup-Cook, Apr 20 '17 at 12:49

A.S.H · Answer 1 · 2017-04-20T13:57:55.820

To refresh your calculation when a new sheet is added (this action by default does not trigger calculation), you can add this to the code module Thisworkbook:

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

Also, you can make your function to always return the last worksheet's name instead of a harcoded Worksheets(4), i.e.

LastSheetName = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name

score 0 · Answer 2 · edited Apr 20 '17 at 13:38

Does something like this work? I was exactly sure what sheet you were trying to put 'Worksheets(4).Name' on so I used Sheet1. I put this in the 'Workbook_NewWorksheet' Event.

It gets the name of the fourth worksheet in the workbook and adds it to Cell A1 of Sheet1 when a worksheet is added to the workbook. This does not take care of when a worksheet is deleted from the workbook. When a woksheet is deleted the Sheet1 Cell A1 doesn't get updated

Private Sub Workbook_NewSheet(ByVal Sht As Object)

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim ws4 As Worksheet
    Set ws4 = ThisWorkbook.Worksheets(4)

    ws.Range("A1").Value = ws4.Name

End Sub

Refresh funtion on worksheet

2 Answers2