You need some method of determining which cells are group names in column A.
You may wantt to put the word "group" in the cell so you can distinquish
between group name and cities.

"David" wrote:

Just a couple things...
The group or section headings for are Ben, One Hour, MS. I just used A, B, C
for examples.
The names in Column A are the City, ST. So under the group/section for Ben,
there might be 20 locations by City, ST in Column A and their location number
in Column B.

Each of the three groups has it's own reporting form. Sheet1 has links to
certain cells on each page with $ Sales amounts. Because each group has a
different format, the links in C, D, F, G, I use different cells depending on
the type of worksheet being used. The worksheets are all the same within each
group, but there are three different worksheets. So the Ben group might look
to cell B31 on the location's worksheet for the info in C14 on sheet1,
(Column C is total monthly sales), but the One group might look to cell C44
on the location worksheet for the same info to go into C25.

"Joel" wrote:

I figured out some easier ways of adding worksheet in proper order

1) I assume the group letter in column A was one character long.
2) Sheet names to copy were one charater long either A, B, C, ...

I didn't understand what you want for these items
1) Update the data links in cells C, D, F, G, I to the correct cells on the
newly named worksheet for that location. (Each group uses different cells on
the three different sheets...if you give me the method, I can update the
cell
addresses for each worksheet type)

What needed to be updated???

2) Update the formulas in cells E, H (sums CD, FG) like the formulas in above
cells.

Can't do without nknow what the formulars are

3) When Deleting a row, just need to delete the the row and it's
corresponding
worksheet.

You need a seperate macro for deleting? Could use worksheet cxhange macro

Sub add_delete()

Sheets("sheet1").Activate
InputRow = InputBox("Enter Row to Add")
AddRowNumber = Val(InputRow)
Sheets("sheet1").Cells(AddRowNumber, "A").EntireRow.Insert Shift:=xlDown

LocationName = InputBox("Enter Location Name")
Sheets("sheet1").Cells(AddRowNumber, "A") = LocationName

LocationNumber = InputBox("Enter Location Number")
Sheets("sheet1").Cells(AddRowNumber, "B") = LocationNumber

'Get Group Name
For RowCount = (AddRowNumber - 1) To 1 Step -1

If Len(Sheets("sheet1").Cells(RowCount, "A")) = 1 Then
GroupName = Sheets("sheet1").Cells(RowCount, "A")
Exit For
End If

Next RowCount

'Get unique worksheet name
OldSuffix = ""
FoundWS = False
For Each ws In Worksheets

If LocationName = Left(ws.Name, Len(LocationName)) Then
'if no suffix on ws name
If LocationName < ws.Name Then
NewSuffix = Right(ws.Name, 1)
Else
NewSuffix = ""
End If
FoundWS = True
'New Suffix greater than old suffix
If StrComp(NewSuffix, OldSuffix) = 1 Then
OldSuffix = NewSuffix
End If
End If
Next ws

If FoundWS = True Then

If OldSuffix = "" Then
Suffix = "A"
Else
Suffix = Chr(Asc(OldSuffix) + 1)
End If
End If

'Count Number of worksheet names in sheet1 before added row
'count is not empty and not A, B, C
WSNumber = 0
For RowCount = 2 To (AddRowNumber - 1)
If Not IsEmpty(Sheets("sheet1").Cells(RowCount, "A")) Then
If Len(Sheets("sheet1").Cells(RowCount, "A")) 1 Then
WSNumber = WSNumber + 1
End If
End If
Next RowCount

'Copy group worksheet
Sheets(Right(GroupName, 1)).Copy After:=Sheets(WSNumber)
If FoundWS = True Then
NewWSName = LocationName + "_" + Suffix
Else
NewWSName = LocationName
End If
ActiveSheet.Name = NewWSName

Sheets("sheet1").Activate
Cells(AddRowNumber, "A").Hyperlinks.Add _
Anchor:=Selection, Address:="", SubAddress:= _
NewWSName + "!A1", TextToDisplay:=NewWSName

End Sub