Insert New Line

C

Clare

Beginner
Hi!
I have a form that enters new information from the user into a
spreadsheet. However, i want a new row added automatically everytime a
new record is added.
Is this possible to add a row using VBA code?
Thanks in advance
 
D

Don Guillett

use a worksheet change event that adds the row when the last field in the
current row is filled in.
 
D

Don Guillett

right click sheet tab>view code>insert this>modify to suit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
Rows(Target.Row + 1).Insert
Rows(1).Copy Rows(Target.Row + 1)
End Sub
 
C

Clare

Don
This is the code I have, and i want this to either be added in a new
row (as there is other information below this line) or add a new row
after the form adds the information to the spreadsheet?


Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets("Jobs").Activate

Range("B7").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = Job_No
Range("B7").Select

End Sub
 
D

Don Guillett

I'm still not quite sure what you want to do.
Do you want to add a row before or after b7?

Range("b1").End(xlDown).Row+1 'finds next row
cells(rows.count,"b").end(xlup).row+1'finds lastrow +1
 
Top