Excel Tab

[SimpleSimon62]

I'm sure its possible to populate the excel tab, from data within the same worksheet, but how do you do it?

 

[Joe4]

Welcome to the Board!

Not sure what you mean. Perhaps if you explain an actual example, it will make more sense.

 

[SimpleSimon62]

I have an excel file with 40 worksheets within. These represent 1 employee with 1 unique input worksheet. So i wish to label the unique worksheet (tab) to John Doe - Input, so that Empl1-Payroll Calc, will be solely dependent on John Doe worksheet name. - hope this makes sense!

-- removed inline image ---

 

[Joe4]

Perhaps the INDIRECT function is what you are looking, which will allow you to build a sheet reference dynamically and then pull from it.
See:http://www.contextures.com/xlFunctions05.html
(especially the part that says "Create a Reference to a Different Sheet").

 

[NewOrderFac33]

This event code, pasted into the codesheet of the appropriate worksheet will change the tab name to whatever the value in cell A1 is changed to.

Could you amend this to suit your needs?

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            If Len(Target.Formula) > 0 Then
                ActiveSheet.Name = Range("A1").Formula
            End If
        End If
    End If


End Sub

Pete

 

[SimpleSimon62]

You do not have permission to perform this action. Please refresh the page and login before trying again.

 

[NewOrderFac33]

What, you couldn't see my code?

 

[Joe4]

You do not have permission to perform this action. Please refresh the page and login before trying again.

Are you being automatically logged out?
If you click on the "Remember Me" check box when logging in, you will not be logged out after a specific period of time.

 

[SimpleSimon62]

Thanks! silly question - where do i place this code ?

 

[NewOrderFac33]

Adding Event Code into a worksheet or the workbook:
1. Copy the code to the Clipboard.
2. Open your workbook
3. [Alt]+[F11] to open the Visual Basic Editor
4. Within “Microsoft Excel Objects”, double click on the worksheet (or “This Workbook”) where you want the code to run.
5. Paste the code with [Control]+v
6. [Alt]+q to exit the Editor, and return to Excel

Cheers

Pete