Autofill formula to next row

[Guest]

I know it's possible......Need to copy formulas in columns (I11:L11), to the
next row using vb code.
This would be done when data is inserted in cells (I11:L11) and carried on
down the page as data is added.

 

[Dave O]

Sub Macro1()
Range("H11:L11").Select
Selection.Copy
Range("H12").Select
ActiveSheet.Paste
End Sub

 

[Guest]

Thanks you for that.
But how can you make it copy and paste to the next empty row in thodse
columns?

 

[Dave O]

Sub Macro1()
Dim LastRow As Long
Dim wks As Worksheet
Set wks = Worksheets(ActiveSheet.Name)

'determine first unused row in col H
With wks
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row +1
End With

Range("H11:L11").Select
Selection.Copy
Range("H" & lastrow).Select
ActiveSheet.Paste
End Sub

 

[Guest]

Thanks very much Dave.
Im sorry to be such a pain. But how can I add your code to the code below.
Iv been trying different ways, with no luck.
I promise then I shall leave you alone!!!

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox2.Text
LastRow.Offset(1, 1).Value = TextBox1.Text
LastRow.Offset(1, 2).Value = TextBox8.Text
LastRow.Offset(1, 3).Value = TextBox3.Text
LastRow.Offset(1, 4).Value = TextBox4.Text
LastRow.Offset(1, 5).Value = TextBox5.Text
LastRow.Offset(1, 6).Value = TextBox6.Text
LastRow.Offset(1, 7).Value = TextBox7.Text



MsgBox "One record written to Data Sheet"

response = MsgBox("Do you want to enter another record?", vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub