Sleeper: Activecell.offset

[Ronmac]

Hi,

I've looked through the forum to try to solve this particular problem, but can't find an answer that is related.

My macro has a userform which allows the user to input data to be placed into specific rows on one of 20 worksheets. It then autosums Column D (on the current worksheet), placing the autosum val at the bottom. I need that figure to be placed into a specific cell on a worksheet called "TOTALS".

The code is picking up the cell into which the last calculation has been placed, which is always one above the autosum cell. I thought that by instructing the cursor to move one cell down, it would pick up the correct cell. However, it isn't. Would anyone have an idea why not?

This is the code I'm trying to get to work properly...


Worksheets("TOTALS").Range(ActiveCell.offset(1,0)).Value = ActiveCell.Value
Worksheets("TOTALS").Select
End Sub

I am a newbie to VBA and am using a book, the net and forums such as this to teach myself. I'd be grateful for any advice on this particular issue.

Regards,

Ron

[Bob Phillips]

If the TOTALS sheet is already active, there is no need to select it.

It seems to me you want



Activecell.Offset(1,0).FormulaR1C1 = "=SUM(R1C:R[-1]C)"

[Ronmac]

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

[Bob Phillips]

I am not sure yet mate, what is happening in Sub CmdPosttoSheet_Click(), and why Sub sum(), why sheet TOTALS?