VBA to add new row

[coliervile]

The following worksheet (Funds) keep track of 5 funds and each day I go in and manually add a new row to at the bottom and the new daily totals. I would like a macro (with a command button) that will add a new row and copy the formulas and formats from the row above and leave the following columns blank (B, E, H, K, and N the fund closing prices).

On the second worksheet (Daily Averages) is a running totals for each day of the week averages. Thats why I don't just run the formulas down on worksheet one for all of the columns.

If someone has an easier way I'm all ears!

Here the worksheets...letsjust say that row 6 is the last filled row in the worksheet.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	G	$ Change	% Change	F	$ Change	% Change	C	$ Change	% Change	S	$ Change	% Change	I	$ Change	% Change	Best	Worst
2	30-Dec-1994	6.1081	0.0000	0.0000	4.9539	0.0000	0.0000	4.1620	0.0000	0.0000	*	*	*	*	*	*	G	G
3	3-Jan-1995	6.1100	0.0019	0.0003	4.9585	0.0046	0.0009	4.1806	0.0186	0.0045	*	*	*	*	*	*	C	G
4	4-Jan-1995	6.1120	0.0020	0.0003	4.9631	0.0046	0.0009	4.1992	0.0186	0.0044	*	*	*	*	*	*	C	G
5	5-Jan-1995	6.1139	0.0019	0.0003	4.9678	0.0047	0.0009	4.1977	(0.0015)	(0.0004)	*	*	*	*	*	*	F	C
6	6-Jan-1995	6.1159	0.0020	0.0003	4.9724	0.0046	0.0009	4.2007	0.0030	0.0007	*	*	*	*	*	*	F	G
Funds

Worksheet 2- Daily Averages

Excel Workbook
	A	B	C	D
1		C	S	I
2	Monday	0.00389	(0.00638)	0.00215
3	Tuesday	0.00517	0.00504	0.00324
4	Wednesday	0.00333	0.01253	0.00849
5	Thursday	0.00236	0.00452	0.00382
6	Friday	0.00042	0.00839	(0.00028)
Daily Averages

 

[coliervile]

I have this code from a userform (see below). How can I can this part of the coding
""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""

to input the new data into the newly added row and not the fixed rows that is currently be used.


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

 

[coliervile]

Here is all the VBA code that adds data to a database but not in a new row below the last filled row. I have two command button on the front of this worksheet named "Add Row" and "Click here to add new fund data".

Under Forms VBA
frmFunds:

Option Explicit

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

module 1:

Sub FindEmptyCell()
If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
End If
End Sub

module 2:

Sub Rectangle1_Click()
frmFunds.Show
End Sub

module 3:

Sub InsertRow()
ActiveCell.Offset(1, 0).EntireRow.Insert
End Sub

module 4:

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)


' row selection based on active cell
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range
' to remove the non-formulas
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

module 5:

Sub InsertRowsAndFillFormulas_caller()
'-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
Call InsertRowsAndFillFormulas
End Sub