Thanks for the reply, XLd, however it didn't work - but that's probably because I'm not sure where to place your solution within the macro code. I've placed the whole macro code below - maybe you'd be kind enough to tell me where it should go?
Is the TOTALS sheet only active once it has been selected? The macro should take the amount entered in the userform, place it on the next available line (in Column D) then autosum that column and place the val at the bottom. I would then like it to copy that cell and paste it into the correct place on the TOTALS worksheet (each worksheet name occupies a row on the TOTALS worksheet).
Many thanks,
Ron
Private Sub CmdPosttoSheet_Click()
'Post to selected sheet.
Dim LastRow As Long
With Worksheets(cboxDept.Value)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(LastRow, 1) = txtDate
.Cells(LastRow, 2) = txtOrder
.Cells(LastRow, 3) = txtItem
.Cells(LastRow, 4) = Val(txtCost)
.Cells(LastRow + 1, 4).Formula = "=SUM(D1:D" & LastRow & ")" 'this is the row that I believe autosums. _
'The "smiley" should not be there. It appears instead of D1 : D (without the spaces either side of the _
colon) whenever I post this code on the web)
.Activate
End With
End Sub
'I'm not sure where to place the code you supplied XLD
Sub sum()
Worksheets("TOTALS").Range(ActiveCell.Offset(1, 0)).FormulaR1C1 = "=sum(R1C:R[-1]C)"
Worksheets("TOTALS").Select
End Sub
Private Sub UserForm_Initialize()
'loads the sheetnames into dropdown
Dim LDate As Date
LDate = Date
Dim ShtName As Worksheet
For Each ShtName In ActiveWorkbook.Worksheets
Select Case ShtName.Name
Case "Main"
'skip these sheets
Case Else
'Add the rest
cboxDept.AddItem ShtName.Name
End Select
Next
txtDate = LDate
End Sub