How do I put the name of a worksheet into a cell in that workshee.

[Guest]

I have created a template that has several identical worksheets in a workbook
with an overall summary sheet that provides a report of the totals from all
worksheets. This is used as an expense system to assign costs to various
clients and provide an overall summary for VAT and employee reimbursement
purposes. Because the client list changes from year to year I have named the
worksheets Client 1, Client 2 etc.

What I want to do is to allow the person administering this sytem to rename
a worksheet and have that name automatically update the "Client name" cell at
the top of the worksheet, and thus be linked through to the summary sheet.
The problem is that I want each worksheet to have its own name - I used the
Microsoft KB to find out how to do this for the active worksheet and this
works fine but I cannot work out how to get this done for each worksheet
individually whether they are active or not.

 

[Guest]

Just had a thought - it really doesn't matter where the Client name is
entered so long as it reflects through to the worksheet name shown on the tab
and to the summary sheet. If it were easier to program the Client name could
be entered in the worksheet cell so long as the worksheet name is updated
automtically to reflect the change.

Alternatively, because each row in the summary sheet is made up of data
linked from the relevant worksheet the name of the worksheet is in the link.
If it could be extracted and put into a cell on the summary sheet I could
link this to the cell on the relevant worksheet.

Just a couple of additional thoughts - I have not got either to work myself.

 

[Bob Phillips]

Robert,

Not really sure I get the whole question, but you can get the sheet name in
a cell with the formula

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Robert, here is my way to do that:

1) Create yout own function, e.g.:

Function MONTHNUMBER(rAnyCell)
Application.Volatile
MONTHNUMBER = rAnyCell.Parent.Name
End Function

2) use this function as formula in cell, e.g.:´
=MONTHNUMBER(A1)

Vlado

 

[Guest]

*****************************
quote:
What I want to do is to allow the person administering this sytem to rename
a worksheet and have that name automatically update the "Client name" cell at
the top of the worksheet,
******************************
As i read you description, you have a Master wsh with client sheet names in
say Column A: Client 1, Client 2, Client 3, etc
Then you have Sheets with the same names. and a cell at the top of each of
these sheets which also has the sheet name: Client 1 etc
In the Master sheet if A4 is Client 1 then cell B4 is linked to a cell on
the Client 1 sheet.
If this is the case, then why rename the sheets each year? Why not just
have a cell in each Client n sheet for the Clients name. Then link a cell
(add a column if needed) in your Master database to the "Name" cell in each
Client sheet which contains the Name. Or better yet, reverse this so that
each year you simply change the Names, as needed, from one location:
directly in the Master sheet by editing the Names in the "Client Name" column.

If none of this makes sense, you can get the integer portion of each sheet
name with this code:

dim i as interger
dim sh as worksheet

for each sh in worksheets
i=Right(sh.Name,1)
msgbox "This sheet is named: Client " & i
next