=INDIR|ECT not working where tab name created by macro

[Mazbuka]

Hope I can explain this alright.

I posted the below code to name the sheet tab as per contents of cell C2

Now in another sheet if I want to refer to the named sheet using =INDIRECT(A2&"!M28") where A2 has the name of the tab, it returns an error...however if I change A2 & the tab name to say xxxx it works.

Is this a known issue?

Sub RenameSheet()

Dim rs As Worksheet

For Each rs In Sheets
rs.Name = rs.Range("c2")
Next rs

End Sub

 

[Fluff]

What is the name of the sheet?
Also if you are making the sheet name the same as C2 why is the formula looking at A2?

 

[Mazbuka]

On sheet 1, I'll list people's names in column a, so A2 will be Fluff
My sheet 2 will be named Fluff by the macro, pulling the name from cell C2
The INDIRECT function will be on Sheet1, looking at cell A2 (on sheet1) which says "fluff", to direct it to that worksheet.

 

[Fluff]

But what is the name of a sheet that doesn't work?

 

[Mazbuka]

Joe Duffy

 

[Fluff]

In that case you need to add an apostrophe before & after the sheet name like

=INDIRECT("'"&A2&"'!M28")