excel, changing 2 sheet names by input on one tab?

Hi,

What I need to create is a pair of two sheets, that as they are copied and re-named the second sheet would be renamed by the first sheet's name with a prefix added.

So if there was a pair of sheets "sheet1" and "Psheet1", and they are copied at the same time and automatically renamed by excel to "sheet1 (2)" and "Psheet2 (2)" or the index of sheet(3) and sheet(4). The user could just rename the first “A” and the second would be renamed by the CONCATENATE function, adding P to the name of the first with resultant sheet name “PA”.

To complicate things, the users currently change the sheet name on the tab it-self, that name is inserted on a cell in the sheet by using the formula.

=MID(CELL("FileName",A1),FIND("]",(CELL("FileName",A1)))+1,999)

So there is actually no change on the work sheet to trigger the input. This is not a requirement but it would be nice that the only input would occur on the tab it self.


thanks,
Scott

I'm not exactly sure what you are after but you can refer to this...

Open a new excel workbook.
Place an 'A' in cell A1.
Add two buttons and leave the macro name as Button1_click & Button2_click.
In the visual Basic editor paste the folowing...


Sub Button1_Click()
Sheets("Sheet1").Name = Range("a1").Text
Sheets("sheet2").Name = "P" + Range("a1").Text


End Sub

Sub Button2_Click()
Sheets(Range("a1").Value).Activate
ActiveSheet.Name = "Sheet1"
Sheets("p" + Range("a1")).Activate
ActiveSheet.Name = "Sheet2"
Sheets("Sheet1").Activate
Range("a1").Select
End Sub


Don't know if this helps..!

Levonau