Excel Tabs equal cell content

Question

I have a workbook with 5 worksheets.

Worksheet 1: Totals

Worksheet 2: "Company 1"

Worksheet 3: "Company 2"

Worksheet 4: "Company 3"

Worksheet 5: "Company 4"

How do I make the tabs of worksheets 2-5 equal the names I will type in on the "totals" worksheet? I want to be able to type the different company names in cells in worksheet 1, and have tabs 2-5 be automatically be renamed. 

 Example: Worksheet 1 contains (A10:H17) titled Wal-mart.

(A19:H26) titled Publix

(A28:H35) titled Windixie

(A37:H44) titled Walgreens

I would like to have Sheet 2 title automatically change from "Company 1" and equal Wal-mart, sheet 3 to equal Publix...etc. each time. I want to save the workbook with generic Company 1 and have it change as need.

Answer 1

1. Copy this code.

    2) Right-Click the first sheet tab

    3) Select "View Code"

    4) Paste the code into the window that appears.

    5) Save the file as a macro-enabled .xlsm file.

    6) Enter values as you described - I have assumed that the entry of the title is into column A, in the first row of each range.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngC As Range

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Value = "" Then Exit Sub

    Set rngC = Range("A10,A19,A28,A37")

    If Intersect(Target, rngC) Is Nothing Then Exit Sub

    Worksheets((Target.Row - 1) / 9 + 1).Name = Target.Value

 End Sub

Answer 2

IT WORKED ABSOLUTELY PERFECT!!!

THANK YOU VERY MUCH FOR TAKING THE TIME TO HELP!