VBA to change Sheet Name based on lookup from cell value.

[jhall488]

Hi guys,

I need help with a VBA code that would look up the contents in a cell on some of my worksheets and then look up that value in a table that I have on a lookup sheet that would then name the original sheet based on the lookup value.

I have attached an example sheet

Sheet1 is lookup
Manager name is in cell A1, lookup this name in the table on sheet1 and then return the abbreviation as the sheet name that the manager name appears.

[mike7952]

This can do what you need

Please Login or Register  to view this content.

[jhall488]

Mike,

Thanks fro quick reply. I entered code into my test sheet and it works great, however when i plugged into my workbook i got an error. The lookup cell is L1 and the sheet with with the table is now called lookup. Here is the code and how I changed it.

Sub abc()
Const shList As String = "Lookup"
Dim ws As Worksheet
Dim shName As String

For Each ws In Worksheets
With ws
If .Name <> shList Then
If Not IsError(Evaluate("=VLOOKUP(" & .Name & "!L1," & shList & "!A:B,2,FALSE)")) Then
shName = Evaluate("=VLOOKUP(" & .Name & "!L1," & shList & "!A:B,2,FALSE)")
.Name = shName
End If
End If
End With
Next
End Sub

The error is a Run tim error 1004

Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by visual basic.

Thanks

[jhall488]

I found the problem. Thanks for ur help.

I have another item i could use ur help with. When I name these sheet there are 2 sheets that will have the same value in L1. One sheet is named the abbreviation as previouly stated, the other sheet is the abbreviation with a space and a F after it. Is there a way to make the names work also. The sheet with the F after always is listed after the reglar sheet. Do you know of a way to accomadate this?

[mike7952]

Do you mean to skip those sheets when looping thru the worksheets?

[jhall488]

yes and then I would want to name them the same as the sheet before it and add a space and F.
So
1st Sheet is MB
2nd Sheet is MB F
3rd Sheet is AB
4th Sheet is AB F

and so on

[mike7952]

I probably would need to see the workbook to get a better visual of your request.

[jhall488]

Here is an updated test file.

Same thing as before with the manager lookup. I just need to name the following sheet the same and add a F to it. The name in Cell A1 will be the same

[mike7952]

Give this a try

Please Login or Register  to view this content.

[jhall488]

It doesnt seem to work. I thought the sheet with the F was in L1 it is actuall in K1. What do I change?

[jhall488]

I just added an extra column to those sheets and hid it. Thank you for you help

[mike7952]

I forgot the single quotes, try this.

Please Login or Register  to view this content.

[jhall488]

do you have the code to reference K1 on that F sheet. If it requires alot more work, I will just insert and hide the row. If you can come up with it quickly i would appreciate it.

[mike7952]

No I cant really think of a way to do that being were referencing L1 to rename the sheet to MA F

[jhall488]

Yea I will just add and hide a column. Thanks alot for your help Mike