Inserting a row and moving a particular cell down one row

[Ronmac]

Hi all,

Column D on a worksheet contains accounting figures. At the bottom of the column (on an empty row one down from the last entry) I have manually set the cell using the AutoSum button to add up the figures above it.

My macro adds data from a userform to the next available row, but eventually reaches the AutoSum cell and overwrites it. Is there a way to insert a new row, and push down the row below it (the one containing the autosum cell) each time new data is entered?

I've tried a number of ways, but none work.

The code in the macro is below:

Private Sub CmdPosttoSheet_Click()
Dim LastRow As Long
With Worksheets(cboxDept.Value)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(LastRow, 1) = txtDate
.Cells(LastRow, 2) = txtItem
.Cells(LastRow, 3) = txtOrder
.Cells(LastRow, 4) = Val(txtCost)
.Activate

End With
End Sub

Private Sub UserForm_Initialize()
Dim ShtName As Worksheet
Dim LDate As String
LDate = Date

For Each ShtName In ActiveWorkbook.Worksheets
Select Case ShtName.Name
Case "Main"
Case Else
cboxDept.AddItem ShtName.Name
End Select
Next

txtDate = LDate
End Sub

Any advice would be appreciated.

Regards,

Ronmac

[GTO]

Try tacking in:

.Cells(LastRow + 1, 4).Formula = "=SUM(D1:D" & LastRow & ")"


Since you are finding LastRow in Col A, this should just overwrite the last '=SUM(...) and enter a new one below the last val in Col D.

Mark

[Ronmac]

Hi Mark,

Many thanks for that - it works perfectly!

You guys are incredible. It would have taken me a month of Sundays to work that out!

Best wishes,

Ron

[Ronmac]

Hi all,

How can I get the macro to copy the autosum cell in each worksheet (the location of which now changes each time data is entered) to the appropriate row on a separate worksheet? They are all in the same workbook.

I tried:

Sheets("TOTALS").Range("B2").Value = _
Sheets("News").Range("D" & Rows.Count).End(xlUp).Value

..and although it placed the value in the correct column on the "Totals" worksheet, it was in the wrong row, ie instead of Row 11 ("News"), it was placed in Row 2 ("Back Bench"), which just happens to be the first worksheet in the book.

I'd appreciate any advice.

Ronmac

[GTO]

I tried:

Sheets("TOTALS").Range("B2").Value = _
Sheets("News").Range("D" & Rows.Count).End(xlUp).Value

..and although it placed the value in the correct column on the "Totals" worksheet, it was in the wrong row, ie instead of Row 11 ("News"), it was placed in Row 2 ("Back Bench"), which just happens to be the first worksheet in the book...

Hi Ronmac,

I feel I must be missing something, as the code indicates you want the value placed in B2 instead of (presumably) B11?

Might have you simply mis-typed where the value is to end up; or what is it that is wanted?

Mark

[Ronmac]

Hi Mark,

If you look at the attached, hopefully you'll see what it is I'm trying to achieve.

"Back Bench" is Sheet1, "City" is Sheet2, and so on. Please run the macro and insert details as follows:

Department: Back Bench
Cost: 10

Do it again, but change the Deparment to one of the others in the list.

What I'd like is that the autosum which appeared at the bottom of Column D on Back Bench (and the one that appeared on the second department you chose) to replicate in Column B on the worksheet named TOTALS. I need to keep a running total on each worksheet which also appears on the TOTALS sheet.

Hopefully I've made sense.

Regards,

Ron

[Ronmac]

Hi all,

Regarding my question above, I've added the code below to my macro which works - with one exception. Instead of picking up the val of the autosum cell at the bottom of Column D, it is finding the one above it and pasting it where I wanted the autosum val to appear.

Would anyone have any idea why it's picking up this cell?

Regards,

Ron

Sub sum()
End Sub
Worksheets("TOTALS").Range(ActiveCell.Address).Value = ActiveCell.Value
Worksheets("TOTALS").Select
End Sub