Insert Row and Corresponding Unique Worksheet

[David]

I'm finishing a project which I could use some MAJOR help with.

I have a workbook with location names (Richmond, Va) on sheet1, column A in
three groups (A, B, C) There are titles, sub-totals, empty rows between the
groups.

I have three named ranges for the numbers in Column B for each group, A, B, C.

Each location in Column A is also a hyperlink to a corresponding worksheet.
The worksheets are in order of the location names. Each location has a unique
location number (123) in column B and data/formulas in columns C-I. (5 data
sources come from constant cells on the corresponding worksheet with sums of
this data on just that row, but each section uses different cells depending
on the format of the worksheet).

Each group has it's own worksheet master format. These masters are at the
end of the worksheets in A, B, C order.

I add/delete many locations to each group and need a macro that will
add/delete rows and the corresponding worksheet, create a hyperlink to the
new worksheet and update the formulas to the correct cells on the worksheet
based on which group/worksheet type was added.

When adding a worksheet, I need a macro that will do the following:

Insert a row based on the row number input by the user (MsgBox)
Insert a location name input by user (MsgBox) in Column A on the new row
Insert a location number input by user (MsgBox) in Column B on the new row
Select/Copy the correct worksheet formatted for the group where the new row
was added. (If in group A, use A worksheet)
Insert the new worksheet in the proper order based on where the new row was
added in the group.
Rename the new worksheet based on the location name in Column A (there may
be duplicate names so the macro needs to check for duplicates and if found
can add a suffix for the group added to, such as Richmond, VA_B if Richmond,
VA exists in group A already. I could do this on all of them to eliminate
checking the other worksheets as they would all be unique.)
Create a hyperlink to the new worksheet in Column A of the new location added.
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)
Update the formulas in cells E, H (sums CD, FG) like the formulas in above
cells.

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

I would really appreciate someone taking this on! Many thanks in advance!

David

 

[Joel]

I don't have time to finish. Here is the start. In column a you should have
the group listed as "Group A", "Group B", "Group C". You can't find just A,
B,C when doinhg the search for the groups.

It is also very hard to add newworksheet in the correct order in the group.
There are losts of combinations to consider. I would recommend that thne
Location Name on sheet 1 include the suffix tp match the worksheet name.
Then it would be easy to add worksheet in correct order.

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 i = (AddRowNumber - 1) To 1 Step -1

If InStr(Sheets("sheet1").Cells(i, "A"), "Group") > 0 Then
GroupName = Sheets("sheet1").Cells(i, "A")
Exit For
End If

Next i

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

If LocationName = Left(ws.Name, Len(LocationName)) Then
NewSuffix = Right(ws.Name, 1)
FoundWS = True
If StrComp(NewSuffix, OldSuffix) Then
OldSuffix = NewSuffix
End If
End If
Next ws

If FoundWS = True Then

If OldSuffix = "" Then
Suffix = "A"
Sheets(Right(GroupName, 1)).Copy After:=Sheets(LocationName)
ActiveSheet.Name = LocationName + Suffix
Else
Suffix = Chr(Asc(OldSuffix) + 1)
Sheets(Right(GroupName, 1)).Copy After:=Sheets(LocationName + OldSuffix)
ActiveSheet.Name = LocationName + Suffix
End If
Else
aftersheet = Sheets("sheet1").Cells(AddRowNumber - 1, "A")
Sheets(Right(GroupName, 1)).Copy _
After:=Sheets(aftersheet)
ActiveSheet.Name = LocationName
End If



End Sub

 

[Joel]

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

 

[David]

Joel,
Thanks so much for taking this on...I'm just starting with it this morning.
I'll let you know how it goes!
Thanks much again!!

David

 

[David]

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

 

[Joel]

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.