Rename a worksheet to match a field

[hroberts29405]

Here is what I have, I workbook, 12 worksheets, I use this for payroll of subcontractors, and with subcontractors coming and going would like to be able to change a worksheet to match the name of the new subcontractor.

Simply I have 1 sheet that is where I enter all of the info and the other sheets are Summary sheets for that sub. I would like to be able to change the name of a subcontractor on my tally sheet and it change the name of the sheet assigned to that sub.

Any help would be great

Harold

 

[joelnichols]

Something like linking a cell with a sheet name? If I get what you mean.

 

[joelnichols]

Something like this help?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Target.Address = " " Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Target.Address = "$A$1" Then Sh.Name = Target
End Sub

In this case A1 contents would name the sheet.

P.S. I ripped this off some guy on MrExcel.

 

[hroberts29405]

I tried it and modified it to look like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Target.Address = " " Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "'Sub Contractors Total'$Ac$1" Then Exit Sub
If Target.Address = "'Sub Contractors Total'$Ac$1" Then Sh.Name = Target
End Sub

I then changed the name in the field on the other worksheet and nothing changed. I have not worked with macros in a very long time and even then it was limited. Do I need to do something to make a macro run, or is it a real time thing?

Harold

 

[joelnichols]

If you put the code in "thisworkbook" the only thing you would need to change is the cell in which the name would be placed. Like so:

VBA auto tab names.xls
	A	B	C	D
1	Dog	Tabs in this sheet are named after the Value of cell A1
2		1
3		2
4		3
Dog

If the subs name was in A4 then you change the code like this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Target.Address = " " Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address<> "$A$4" Then Exit Sub
If Target.Address = "$A$4" Then Sh.Name = Target
End Sub

Am I getting warm or still missing your point?

 

[hroberts29405]

I think you are right on I am just missing as where exactly to put this in "thisworkbook" where does that mean? right click on the sheet and view code, and place it there or somewhere else?

Harold

 

[joelnichols]

When you right-click and choose view code, on the left should be the project window. If you only have 1 book open (hope so for simpler route) you should see VBAproject ("your workbook name here"). Within that "tree" of items should be a list containing all the sheet names in your workbook and another one named "thisworkbook". Still with me? The code goes in "thisworkbook".

 

[hroberts29405]

Ok I have placed it there and changed the cell field to be F4 due to I have a formula in a1 already that I don't want to lose. on the worksheet I am looking at I change the f4 field to harold and nothing happens.

What am I doing wrong?

does this only work with a name on the current sheet or can all of the names be on another sheet and not in sequence?

for example I have the subs name's in a1 c1 e1 g1 i1... and so forth on a sheet titled sub contractor totals

 

[hroberts29405]

I have it changing to a cell on the current page where I manually input the data, now how about if that field has a formula pulling the name from another sheet?

Harold

P.S. Thank-you very much for your help on this. This is one of those things I have been having to change manually almost everyweek, and I have over 200 subcontractors. When I have to make a lot of changes it gets very time consuming?

 

[joelnichols]

The code was written to make all the sheet names to be from the same cell. Im afraid altering that is over my head. Alot of the board regulars can solve this im sure. Sorry If you can get a common cell on all pages the code would work I think. Try maybe an unused cell near the bottom of each sheet and put ="where subs name is in each sheet"?