Insert New Line

[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

[Don Guillett]

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Clare" <Clare.2ah0lb_1152100202.1295@excelforum-nospam.com> wrote in
message news:Clare.2ah0lb_1152100202.1295@excelforum-nospam.com...
>
> 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
>
>
> --
> Clare
> ------------------------------------------------------------------------
> Clare's Profile:
> http://www.excelforum.com/member.php...o&userid=36047
> View this thread: http://www.excelforum.com/showthread...hreadid=558384
>

[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


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:umvkOBDoGHA.1248@TK2MSFTNGP05.phx.gbl...
> use a worksheet change event that adds the row when the last field in the
> current row is filled in.
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Clare" <Clare.2ah0lb_1152100202.1295@excelforum-nospam.com> wrote in
> message news:Clare.2ah0lb_1152100202.1295@excelforum-nospam.com...
>>
>> 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
>>
>>
>> --
>> Clare
>> ------------------------------------------------------------------------
>> Clare's Profile:
>> http://www.excelforum.com/member.php...o&userid=36047
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=558384
>>
>
>

[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

[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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Clare" <Clare.2ah7jd_1152109207.0284@excelforum-nospam.com> wrote in
message news:Clare.2ah7jd_1152109207.0284@excelforum-nospam.com...
>
> 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
>
>
> --
> Clare
> ------------------------------------------------------------------------
> Clare's Profile:
> http://www.excelforum.com/member.php...o&userid=36047
> View this thread: http://www.excelforum.com/showthread...hreadid=558384
>