Name Sheet Based on Results of the formula

[BIGTONE559]

I'm looking to name sheets based on the results of a vlookup in a cell.

I have a Vlookup formula in cell "C4". I run a series of code that will determine whether or not the sheet will be renamed to the value in C4 or the Sheet index number. However, my code errors out because it's referring to the formula and that it cannot name the sheet the formula because it's too long.

Please Help

 

[rafaelspeixoto]

Try this:

FResult = sheet(1).range("C4").value
sheet(1).name = FResult

 

[BIGTONE559]

Try this:

FResult = sheet(1).range("C3").value
sheet(1).name = FResult

Still No Luck. Says invalid name.

Value in "C3"=IF(C4="","",VLOOKUP(C4,Table_EMPLOYEE,2,FALSE))

Code:

Sub AMaccro()
Dim i As Integer
Dim aSh As Worksheet

Set aSh = ActiveSheet

i = ActiveSheet.Index

FResult = Sheets(i).Range("C3").Value
MsgBox (FResult)

Worksheets(i).Name = FResult

End Sub

 

[rafaelspeixoto]

run the macro step by step (with F8) and check what is the name that is causing the error. Chances are you are trying to rename the sheet with an invalid name or a name with invalis char, like . or ,

 

[BIGTONE559]

run the macro step by step (with F8) and check what is the name that is causing the error. Chances are you are trying to rename the sheet with an invalid name or a name with invalis char, like . or ,

I'm thinking that it's trying to name the sheet with the formula located in C3. I'm wondering if i can store the result as a string? after some testing i did notice that the fact that C3:F3 had been merged was an issue.

 

[Norie]

You are storing the result as a string here:

FResult = Sheets(i).Range("C3").Value

There's even a message box to display the value.

Check to see if the value is a valid sheet name.

 

[BIGTONE559]

You are storing the result as a string here:

FResult = Sheets(i).Range("C3").Value

There's even a message box to display the value.

Check to see if the value is a valid sheet name.

the returned value is "Mr. Potatohead" so i don't see how that is a problem.

when the message box pops up it displays the value in C3 perfectly. I'm wondering is it a problem that i have the code being ran via "ThisWorkbook" as a private sub????

 

[Matt Rogers]

Hello,

Maybe it helps.
The value in a1 = the sheet name:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Out
If Target = Range("A1") Then ActiveSheet.Name = Range("A1").Value
Out:
End Sub

 

[Norie]

What do you mean the code is being run via ThisWorkbook?

This code should be in a standard module, not a workbook or worksheet module.

 

[BIGTONE559]

Hello,

Maybe it helps.
The value in a1 = the sheet name:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Out
If Target = Range("A1") Then ActiveSheet.Name = Range("A1").Value
Out:
End Sub

Found the problem!!!!

seems there was a default field size on the field that was being referenced to from access. I trimmed the appropriate field text and it works wonderfully!!

I appreciate all the feedback!