PDA

View Full Version : Copy cell format, formulas, and insert new row



coliervile
11-03-2007, 05:27 AM
I have a worksheet to track investment funds. I made userform that would be used to update daily changes. When I update the data I want a macro that will copy the cell format amd formulas and insert a new row below Row 2. Another way I could do this is have a Add Row button (already on sheet) and once the button is clicked it will copy the cell format and formulas and insert a new row and update the funds that way?My knowledge base is limited and would appreciate your help.Best regards,Charlie

coliervile
11-03-2007, 05:52 AM
Here's the userform coding. Having trouble uploading file.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Funds")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter date"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtGFund.Value
ws.Cells(iRow, 5).Value = Me.txtFFund.Value
ws.Cells(iRow, 8).Value = Me.txtCFund.Value
ws.Cells(iRow, 11).Value = Me.txtSFund.Value
ws.Cells(iRow, 14).Value = Me.txtIFund.Value
'clear the data
Me.txtDate.Value = ""
Me.txtGFund.Value = ""
Me.txtFFund.Value = ""
Me.txtCFund.Value = ""
Me.txtSFund.Value = ""
Me.txtIFund.Value = ""
Me.txtDate.SetFocus
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub txtDate_Change()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub


Best regards,

Charlie

lucas
11-03-2007, 07:40 AM
Hi Charlie,
Why not just format the entire sheet and add the formula's and let the userform add the values in the next row as required?