Solved: Inserting Rows

[zoom38]

Im trying to insert a copied row but the cell references are not changing properly. I've spent a long time on it but I can't figure it out. Ive attached a short version of my spreadsheet. The problem is when press the button "Click Here To Add A New Member". the code copies the 2nd row up from the bottom on the "Totals" sheet and inserts it just below it, but the cell references don't change like they do normally in a copy and paste routine. Also the cell references in the row that gets copied get messed up. Please take a look.
Thanks
Gary

[zoom38]

In my haste I ruined the attachment. Im attaching one that has the right formulas. I appologize to those who opened it.

[acw]

Hi

The insert rows are causing your problem.

With the total sheet, insert the new row above the blank row, then take a copy of the last formula row and put it in the new row. More like
[vba]
Rows(LastRow - 1).Select
Selection.Insert Shift:=xlDown
Rows(LastRow - 2).Copy Destination:=Rows(LastRow - 1)
[/vba]

With the monthly sheets, determine the last existing row, then take a copy of that and put it into the next row. Don't do an insert! Once you have the new row, then fill with zeros as you are currently doing. This should keep your existing formulas intact.


HTH

Tony

[zoom38]

Thanks Tony that worked. One other thing though, did you notice that the cell references in the formulas in the bottom row "Totals" do not adjust? Does this mean I have to adjust the formulas or did I miss something.

Also my sort doesn't work right, what did I miss on this one?

[VBA]Sub SortMonthlyWorkSheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Totals" And ws.Name <> "Perf. Eval." Then
ws.Activate

'Find The Last Row
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

'Sort The Monthly Worksheets By Member Name
Range(Cells(5, 1), Cells(LastRow, 15)).Sort Key1:=Range("A5"), _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'Move To Cell A1
Range("a1").Select

End If
Next ws

End Sub
[/VBA]

Thanks again.

[johnske]

...the cell references in the formulas in the bottom row "Totals" do not adjust? Does this mean I have to adjust the formulas or did I miss something...

Yes,

Let's suppose your worksheet code is of the form K21=SUM(E9:H20,J9:J20). After inserting a new row above K21 the sum shown in K21 will remain unchanged.

You'll need to change the worksheet formulas to use OFFSET so they're of the form K21=SUM(E9:OFFSET(H21,-1,0),J9:OFFSET(J21,-1,0))

[zoom38]

Thanks Johnske I wasn't aware of the offset function.

[acw]

Hi

I've made a couple of changes to the macros for the add a member, and the formulas in the sheets.

Have a look at the attached, and see if you can use this.

Tony

[zoom38]

Thanks Tony, I like your use of the Choose & Match functions instead of my lengthy nested IF statements. Also thanks for tidying up the code in the macros. Im having a little trouble trying to use your aaa macro to fix the formulas in the bottom row on the "Totals" worksheet after inserting a new member/row. Oh yeah, thanks for the drop down list for the months, pretty nifty. How do I edit that? How do I create one of those?

Thanks
Gary

[acw]

Gary

1) An easy fix for the formula is to make it include the blank row immediately above the total line. That way, when the new row is inserted, the formula will automatically adjust.

2) Dropdown: Select B1, then Data, Validation. You will see that there is a comma separated list which you can edit.

3) Dropdown: As above, select the cell, then data, validiation, List. You can either type in a comma separated list, or use a formula to refer to a name, or a defined name.

Another thing, I don't think I completed all the options available in the sheet. Missed out the Next January sheet and I think the YTD only went to June. Make sure the sheets are in the correct order when you do this type of sum.


Tony

[zoom38]

Tony thanks for the info on the dropdown. I took care of the year to date formulas through december and Next Jan is only for the Perf. Eval. sheet. I've spent hours on it but I still can't figure out the Last row formulas (Totals) using offset or any other way for that matter.

Gary

[acw]

Gary

If you have data in rows 2-20, a blank row in 21 and the total formulas in row 22 being =sum(b2:b20), then change the formula to include the blank row =sum(b2:b21). Now when the new row is inserted ahead of row 21, the sum formulas will automatically adjust for the inserted row.


Tony

[johnske]

Tony thanks for the info on the dropdown. I took care of the year to date formulas through december and Next Jan is only for the Perf. Eval. sheet. I've spent hours on it but I still can't figure out the Last row formulas (Totals) using offset or any other way for that matter.

Gary

Hi Gary,

I just looked at your original attachment. Just type in =SUM(B5:OFFSET(B20,-2,0)) in B20 and then fill across...

HTH

[zoom38]

Thank you jonske, I didnt even try that I was attempting to do it in vba. I dont know why but i thought the worksheet function wouldn't work.
Thanks again
Gary

[zoom38]

Just when I thought I was done I found another hurdle that I can't get over. When I activate the delete a member/row macro the references don't change properly. I tried using the offset function but I can't get it.

ex: cell c11 is the following:

[VBA]
=CHOOSE(MATCH($B$1,{"January","February","March","April","May","June","July ","August","September","October","November","December"},0),SUM(Jan!B11),SUM (Jan:Feb!B11),SUM(Jan:Mar!B11),SUM(Jan:Apr!B11),SUM(Jan:May!B11),SUM(Jan:Ju ne!B11),SUM(Jan:July!B11),SUM(Jan:Aug!B11),SUM(Jan:Sept!B11),SUM(Jan:Oct!B1 1),SUM(Jan:Nov!B11),SUM(Janec!B11)) [/VBA]

When I delete row 11 the new formula in row 11 which was in row 12 is the following:

[VBA]
=CHOOSE(MATCH($B$1,{"January","February","March","April","May","June","July ","August","September","October","November","December"},0),SUM(Jan!B11),SUM (Jan:Feb!B12),SUM(Jan:Mar!B12),SUM(Jan:Apr!B12),SUM(Jan:May!B12),SUM(Jan:Ju ne!B12),SUM(Jan:July!B12),SUM(Jan:Aug!B12),SUM(Jan:Sept!B12),SUM(Jan:Oct!B1 2),SUM(Jan:Nov!B12),SUM(Janec!B12))
[/VBA]

The references now should all be row 11 but some have remained as row 12 and all of the following rows have the same problem. I tried using offset to fix this but I don't think I have a grasp on offset yet because I couldn't get it to correct this.
I appologize for being a pain on this one, any help would be appreciated.

Thanks
Gary

[johnske]

Use $B$11 to keep it fixed at that reference

[zoom38]

jonske, using absolute references doesn't work in this instance. Say my worksheet has 20 rows of names in column A with formulas through column AC. If I activate my delete a member macro it asks for the members name to delete. If I choose a name that happens to be in the 11th row it deletes the 11th row and what was in the 12th row now becomes the 11th row. This is where the problem lies.

This:
[VBA]
12

=CHOOSE(MATCH($B$1,{"January","February","March","April","May","June","July ","August","September","October","November","December"},0),SUM(Jan!B11),SUM (Jan:Feb!B11),SUM(Jan:Mar!B11),SUM(Jan:Apr!B11),SUM(Jan:May!B11),SUM(Jan:Ju ne!B11),SUM(Jan:July!B11),SUM(Jan:Aug!B11),SUM(Jan:Sept!B11),SUM(Jan:Oct!B1 1),SUM(Jan:Nov!B11),SUM(Janec!B11))
[/VBA]


Becomes this:
[VBA]
=CHOOSE(MATCH($B$1,{"January","February","March","April","May","June","July ","August","September","October","November","December"},0),SUM(Jan!B11),SUM (Jan:Feb!B12),SUM(Jan:Mar!B12),SUM(Jan:Apr!B12),SUM(Jan:May!B12),SUM(Jan:Ju ne!B12),SUM(Jan:July!B12),SUM(Jan:Aug!B12),SUM(Jan:Sept!B12),SUM(Jan:Oct!B1 2),SUM(Jan:Nov!B12),SUM(Janec!B12))

[/VBA]
I need what was in the 12th row to now reference what is in the 11th row.


Gary

[acw]

Gary

The references should all sort themselves out when you delete the data from all the sheets. Deleteing from one sheet will adjust for the formula to that sheet only. I'm guessing that you have removed an entry for the Jan sheet, but you haven't actioned for the Feb, Mar etc sheets. What happens when you remove the entry from the other sheets?


Tony

[zoom38]

Tony, I have a routine that loops thru the worksheets except the Totals worksheet and it deletes the corresponding row on each worksheet. Then it calls a routine to delete the appropriate row on the Totals worksheet. I have to delete the row from the Totals worksheet last because the names are on that sheet and the other sheets get the names from that sheet. No matter what I do the cell references get messed up on the Totals and Perf Eval worksheets. Here is my delete routine.

[VBA]
Public DeleteMember As String

Sub RemoveMemberMain()

Application.ScreenUpdating = False
Call RemoveMemberFromMonthlyWorksheets
Call RemoveMemberFromTotalsWorksheet
Application.ScreenUpdating = True

End Sub


Sub RemoveMemberFromMonthlyWorksheets()

Dim ws As Worksheet
Dim LastRow As Long
Dim c As Range
Dim FoundIt As String

'Input Box To Enter The Member To Be Deleted
DeleteMember = InputBox("Enter The Last Name Of The Member To Be Deleted.", "DeleteMember", "Last Name")

'Loop Through The Sheets Except "Totals" Deleting
'The Row That Contains The Member That Was Input
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Totals" Then
ws.Activate

'Find The Last Row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find The Member To Be Deleted
Set c = Range(Cells(5, 1), Cells(LastRow, 1)).Find(what:=(DeleteMember))
FoundIt = c.Address
c.Activate
c.EntireRow.Delete

'Move To Cell A1
Range("a1").Select
End If
Next ws
End Sub


Sub RemoveMemberFromTotalsWorksheet()

Dim LastRow As Long
Dim c As Range
Dim FoundIt As String


'Select "Totals" Worksheet
Sheets("Totals").Select

'Find The Last Row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find The Member To Be Deleted

Set c = Range(Cells(5, 1), Cells(LastRow, 1)).Find(what:=(DeleteMember), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
FoundIt = c.Address
c.Activate
ActiveCell.ClearContents

'Sort The Names In Column A
Call SortTotalsWorkSheet

'Delete The Row 2 Up From The Last Row
With ActiveSheet
.Range(LastRow - 2 & ":" & LastRow - 2).EntireRow.Delete
End With

'Move To Cell A1
Range("a1").Select

End Sub

[/VBA]

Im attaching the current file so you can see. I've been deleting "joe" as a test which is in row 11. I know my coding is primitive but it is the best I can do. Please try it Im extremely frustrated.

Gary

[acw]

Gary

Try the attached. Ended up having to rebuild the formulas and reapply.

Tony

[zoom38]

Thanks Tony, works perfectly. I modified your Totals sheet routine into one for the Perf eval sheet which works fantastic. Thanks again for spending so much time on this.

Gary