<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Worksheet Names Stored In Cells For Excel Formula References

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

VBA - Worksheet Names From Cells In Excel Formulas

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 LISTDownload 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:

=OFFSET($B$2,0,0,COUNTA($B$2:$B$20),1)

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 WithEnd 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 is added.

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 IfEnd 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 0End 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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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