Tying a Sheet Name to a Cell within a Macro

[saschmeling]

Hi All,

I saw the earlier post of tying a sheet name to a Cell but I am creating a Macro that will take data and split it into 7 separate workbooks then take the data that is split and create new sheets that will sum up the data and add pivot tables to the data.

This will all be done automatically- so What I am really looking for is a bit of code that I can place into an existing Macro with a Loop to tie the sheet names to a cell. This is a Macro that is being stored at the Personal Macro Workbook level.

Does anyone have any ideas or suggestions?

Thanks,
Scott

 

[sdruley]

ActiveCell.FormulaR1C1 = _
"=RIGHT(CELL(""filename"",R[-32]C[-50]),LEN(CELL(""filename"",R[-32]C[-50]))-FIND(""]"",CELL(""filename"",R[-32]C[-50]),1))"

will put the sheet name in AY33, for example

 

[saschmeling]

ActiveCell.FormulaR1C1 = _
"=RIGHT(CELL(""filename"",R[-32]C[-50]),LEN(CELL(""filename"",R[-32]C[-50]))-FIND(""]"",CELL(""filename"",R[-32]C[-50]),1))"

will put the sheet name in AY33, for example

Thanks, But that is the reverse of what I am trying to do. I want to have the sheet name match what is in a cell. So if Cell A1 states "Adults" I want the sheet tab to change from "Sheet 1" to "Adults".

Hope that makes it more clear.

 

[Norie]

Try this.

For Each ws In ThisWorkbook.Worksheets
     ws.Name = ws.Range("A1").Value
Next ws

 

[saschmeling]

Try this.

For Each ws In ThisWorkbook.Worksheets
     ws.Name = ws.Range("A1").Value
Next ws

Thanks- That worked perfectly and just what I was looking for.

 

[saschmeling]

Hi Norie,

Is there a way to make this so that each sheet can reference a different cell address. So Sheet1 references Cell A7, Sheet2 references Cell A1, Sheet3 references Cell A1.

Thanks,
Scott

 

[Norie]

Scott

You would need a way for the code to know which cell in which sheet to use for the name.