Got any Excel Questions? Free Excel Help
IMPORTANT. To understand this page read this page first. Variable Worksheet Names in Excel Formulas
DYNAMIC WORKSHEET NAME LIST
Download Workbook Example
The first thing we should do is create a dynamic named range for our list of Worksheet names. Go to Insert>Name>Define and use the name "Worksheet_Names" and have it refer to:
Next we need a macro that we can Call which will add the 2 needed formulae.
One that adds the CELL formula automatically when we add a new Worksheet.
One that adds the MID and FIND formula that parses out the Worksheet name from the CELL formula.
The code for this goes in a standard public module and is as shown below
Sub AddCellFormulas(strShName As String) With Sheet1.Cells(Rows.Count, 1).End(xlUp) .Offset(1, 0).FormulaR1C1 = _ "=CELL(""filename""," & strShName & "!R1C1)" .Offset(1, 1).FormulaR1C1 = _ "=""'""&MID(RC[-1],FIND(""]"",RC[-1])+1,256)&""'!""" End With End Sub
"Sheet1" is the Worksheets CodeName
Note the Procedure takes an
argument as a String and this String is then used in the CELL formula that
Next we need to access the Private Module of the Workbook Object (ThisWorkbook). If in the VBE. Simply double click ThisWorkbook. If in Excel proper, right click on Excel icon, top left next to File and choose View Code. It is in here we use the code below.
Private Sub Workbook_NewSheet(ByVal Sh As Object) If Sh.Type = xlWorksheet Then Call AddCellFormulas(Sh.Name) End If End Sub
In the Event code (which fires
anytime a Sheet is added) it first checks to ensure the Sheet Type is a
standard Excel Worksheet. If it is, the code uses Call to run the Procedure
"AddCellFormulas" and parses the all important worksheet name to it.
Next we need some more Event code that will clear out any redundant formula (added by "AddCellFormulas") when/if a Worksheet is deleted. If a worksheet is deleted the CELL formula that used its name within will result in #REF! as the Worksheet no longer exists. This will result in the MID and FIND formula also returning #REF! as it references a #REF! cell. With this in mind we can use the Calculate Event of the Worksheet Object housing these formulae to clear any error cells. Right click on the Worksheet name (one that AddCellFormulas adds the formulas to) and choose View Code. In here paste the code below.
Private Sub Worksheet_Calculate() On Error Resume Next Application.EnableEvents = False With Me.UsedRange .SpecialCells(xlCellTypeFormulas, xlErrors).Clear .Sort Key1:=Range("B2"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End With Application.EnableEvents = True On Error GoTo 0 End Sub
Now, each time we add a new Worksheet, we will have the new name in the Validation list. Anytime we delete a Worksheet its name will be cleared and the Worksheet sorted to prevent blanks.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates