 |
| FREE Excel STUFF |
Search |
| PRODUCTS |
| Development |
| Contact |
Excel Online |
|
Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee FREE DOWNLOADS SmartVBA | SmartVB6 | CodeVBA | CodeVB6 | Code Generator Pro | TraderXL Pro Package. MORE.. |
Current Special! Complete Excel
Excel
Training Course
for Excel 97 - Excel 2003, only $145.00.
$59.95 Instant
Buy/Download,
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
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:
=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 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
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 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
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 special@ozgrid.com 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
