reference tab name

[kenpcli]

I have several worksheets in my workbook, I need cell A2 to reference the tab or worksheet name of that worksheet. for example, worksheet 1 is called summary in cell a2 I need it to say summary, worksheet 2 is called ALB and need cell a2 in that worksheet to say ALB.

Thanks

 

[Legacy 436357]

Maybe try:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

 

[kenpcli]

I did that, but each cell takes on the name of the last tab I updated.

 

[Legacy 436357]

This formula will put the current sheet name in the cell of your choice. Isn't that what you wanted?

 

[Legacy 436357]

You can also have an index of all worksheets in the workbook with hyperlinks to those sheets on your Summary sheet.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.

<code class="vb keyword">Sub</code> <code class="vb plain">CreateIndex()</code>



<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xAlerts </code><code class="vb keyword">As</code> <code class="vb keyword">Boolean</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">I  </code><code class="vb keyword">As</code> <code class="vb keyword">Long</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xShtSummary </code><code class="vb keyword">As</code> <code class="vb plain">Worksheet</code>

<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xSht </code><code class="vb keyword">As</code> <code class="vb keyword">Variant</code>
<code class="vb spaces">    </code><code class="vb plain">xAlerts = Application.DisplayAlerts</code>
<code class="vb spaces">    </code><code class="vb plain">Application.DisplayAlerts = </code><code class="vb keyword">False</code>

<code class="vb spaces">    </code><code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb spaces">    </code><code class="vb plain">Sheets(</code><code class="vb string">"Summary"</code><code class="vb plain">).Delete</code>

<code class="vb spaces">    </code><code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">GoTo</code> <code class="vb plain">0</code>
<code class="vb spaces">    </code><code class="vb keyword">Set</code> <code class="vb plain">xShtSummary = Sheets.Add(Sheets(1))</code>

<code class="vb spaces">    </code><code class="vb plain">xShtSummary.Name = </code><code class="vb string">"Summary"</code>

<code class="vb spaces">    </code><code class="vb plain">I = 1</code>
<code class="vb spaces">    </code><code class="vb plain">Cells(1, 1).Value = </code><code class="vb string">"INDEX"</code>

<code class="vb spaces">    </code><code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">xSht </code><code class="vb keyword">In</code> <code class="vb plain">ThisWorkbook.Sheets</code>

<code class="vb spaces">        </code><code class="vb keyword">If</code> <code class="vb plain">xSht.Name <> </code><code class="vb string">"Summary"</code> <code class="vb keyword">Then</code>

<code class="vb spaces">            </code><code class="vb plain">I = I + 1</code>

<code class="vb spaces">            </code><code class="vb plain">xShtSummary.Hyperlinks.Add Cells(I, 1), </code><code class="vb string">""</code><code class="vb plain">, </code><code class="vb string">"'"</code> <code class="vb plain">& xSht.Name & </code><code class="vb string">"'!A1"</code><code class="vb plain">, , xSht.Name</code>

<code class="vb spaces">        </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces">    </code><code class="vb keyword">Next</code>

<code class="vb spaces">    </code><code class="vb plain">Application.DisplayAlerts = xAlerts</code>

<code class="vb keyword">End</code> <code class="vb keyword">Sub

</code>3. Then press F5 key to execute this code, and the worksheet named “Summary” will include sheet names with hyperlinks.
<code class="vb keyword"></code>