Vba code to sheet name equal cell name

[Lacan]

Simple Question:

Have in cell B2 the name of my customer that would like that sheet name return the same equal name in B2.

How to do so?

Thanks for the great advice.

 

[hiker95]

However my last question: - is theres any way to do it automatically instead of everytime I rename B2 have to do Run Macro Dialog?

Lacan,

We can probably use the Worksheet Change event in each worksheet.

Be back in a little while.

 

[hiker95]

Lacan,

Here is a Worksheet Change event that can go in to each worksheet.

Sample Sheet1:

Excel 2007
	B
2	Sheet1
Sheet1

And, if I change cell B2 to Customer1, we get this:

Excel 2007
	B
2	Customer1
Customer1

And, if I change cell B2 to Lucan, we get this:

Excel 2007
	B
2	Lucan
Lucan

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/29/2016, ME982790
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  With ActiveSheet
    If Not .Range("B2") = vbEmpty Then
      .Name = .Range("B2").Value
    End If
  End With
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Then make changes to cell B2.

 

[igold]

I am glad that Hiker could get you where you needed to be.

Thanks for the feedback. Glad we could help.

 

[pile-it Mark]

can this be modified to do the same to the left footer, referencing "N2" as the data different for each sheet in the workbook?

 

[hiker95]

I am glad that Hiker could get you where you needed to be.

igold,

Thanks for that.

 

[hiker95]

can this be modified to do the same to the left footer, referencing "N2" as the data different for each sheet in the workbook?

pile-it Mark,

Can we have some more information in reference to the above quote?

 

[pile-it Mark]

I have a workbook used to send flights to the accounting department. it consists of a master sheet that is copied and filled in with the flight information and does the computations.

starting a new year with a new workbook. master sheet is "Flight Log 2017 Original" mods to the sheet are r1, r2... just so i can track changes.

first trip will be 2017-01 that is manual input to N2,
trip start date is C10 in mm/dd/yyyy format

the tab name goes in the center footer and file name in right footer. thru normal insert

i would like left footer to print the trip number for the individual sheet based on the value in cell N2 - 2007-01 - for the first trip. the next time i would copy the master it would be 2017-02...

i would like the tab to take its name from either C10 or manual input from N1 if possible

when in added the micro it remains as 2017-01 even when i change cell N2 on the master.

I usually avoid anything except formulas since in never tried to use micros. the ones i use, have been spoon fed to me from this site. and i appreciate it!

 

[pile-it Mark]

after much playing. i have the trip number posting to the center footer. but i have to open and run the macro everytime to make the changes.


this is in the workbook tab:

Private Sub Worksheet_Calculate()
If Range("n1").Text <> Me.Name Then Me.Name = Range("n1").Text
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("N2")) Is Nothing Then
On Error GoTo Quit
Me.Name = Format(Range("N2").Value, Range("N2").NumberFormat)
On Error GoTo 0
End If
Exit Sub

 

[igold]

@Hiker95

No problem, you got him going, you should be recognized for that. Cheers!

igold

 

[hiker95]

pile-it Mark,

I can not follow what you have described in your replies #18, and, #19.

Can I see your workbook/worksheets?


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com