how to rename a tab using a reference cell?

[codedreamer]

Hello,


I have a workbook with 20 tabs that need to be rename according to the contents of cell a1. The problem I face is that cell a1 gets its information from another worksheet. I know I can use the indirect function, but I really do not know how.

Sub RenameTab()

For Each ws In ThisWorkbook.Worksheets

ws.Name = ws.indirect.Value(a1)

Next

End Sub


Thanks for your help,

Codedreamer

 

[Hui]

Ana


Your confusing the Excel spreadsheet functions with the VBA functions

They are quite discrete and separate although you can use the Worksheet functions within VBA if you really want to


The worksheet function Indirect will place the name of the worksheet in A1 using a formula

VBA only need lookup the value from A1 on each sheet and it will return the right value

[pre]

Sub RenameTab()
For Each ws In ThisWorkbook.Worksheets
ws.Name = ws.Range("a1").Value
Next
End Sub

[/pre]

 

[codedreamer]

Thank you Hui, I did tried that, but I got an error message. I thought it was because cell a1 contains a link = purchaselist1!e3, so I was trying to get the value that is originaly place in e3. Since it is 20 different values I was trying to use vba to speed the process. Is there a way to get that value?

Thanks,


codedreamer

 

[Hui]

Ana


When you say it is 20 different values do you mean the sheet name is choosen based on some criteria and looked up from a table


Can you post a sample worksheet to explain what your trying to do?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[codedreamer]

Hi, here is the link

I hope I did this right. This is just a piece of the actual workbook

https://www.dropbox.com/s/0ds3y8a98vy6tz6/Bake%20Sale.xlsx


The diferent values correspond to customer's names, that will change periodically. The names are in a sheet name Purchaselist, then each sheet will have the customer name and what they have bought. I wanted to rename the sheet tabs with the customer's name.

Thank you for your help.

 

[Hui]

Ana


Copy the following code into the Code Module for the Purchaselist worksheet

delete the existing code in the code module


Whenever you change a value in the the Customers column in the Purchaselist worksheet it will update the worksheet names

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Purchaselist" Then ws.Name = ws.Range("a1").Value
Next
End Sub

[/pre]

 

[codedreamer]

Hi Hui,


I tried the code in the example worksheet and it worked perfect, but when I run it in the original it gives me an object define error. The only difference is tha the cell I am referencing is a merge cell. Could this be the cause of it?

Thanks,


codedreamer

 

[Hui]

You could try unmerging the cell

 

[codedreamer]

Thanks a lot Hui, I typed the wrong cell number. It works perfect!


Codedreamer

 

[Hui]

Glad to assist