Ronmac
11-04-2009, 05:33 PM
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
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