Need formula that will allow a number e.g. 5 to be placed in a cell, which
t\will then insert 5 rows. Thanks

That needs VBA.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Resize(.Value).EntireRow.Insert
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

"Carol49" wrote in message
...
Need formula that will allow a number e.g. 5 to be placed in a cell, which
t\will then insert 5 rows. Thanks

"Carol49" wrote:

Need formula that will allow a number e.g. 5 to be placed in a cell, which
t\will then insert 5 rows. Thanks
Formula is what I am looking for, but I am getting a syntax error in the line
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
I have copied and pasted, should I be doing something else? Thanks

Have you inserted it in a worksheet code module as instructed?

--
HTH

Bob Phillips

"Carol49" wrote in message
...


"Carol49" wrote:

Need formula that will allow a number e.g. 5 to be placed in a cell,
which
t\will then insert 5 rows. Thanks
Formula is what I am looking for, but I am getting a syntax error in the
line
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
I have copied and pasted, should I be doing something else? Thanks

"Bob Phillips" wrote:

Have you inserted it in a worksheet code module as instructed?

--
HTH

Bob Phillips

"Carol49" wrote in message
...


"Carol49" wrote:

Need formula that will allow a number e.g. 5 to be placed in a cell,
which
t\will then insert 5 rows. Thanks
Formula is what I am looking for, but I am getting a syntax error in the
line
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
I have copied and pasted, should I be doing something else? Thanks


Yes, I am right clicking on sheet tab and clicking on View Code and then pasting in formula. Thgat line always comes up red and when I go to enter in the appropriate cells on the correct sheet I gfet the Compile Error - Syntax Error. Thanks

I am confused, it is fine here.

You say you pasted the formula. What exactly have you copied in?

--
HTH

Bob Phillips

"Carol49" wrote in message
...


"Bob Phillips" wrote:

Have you inserted it in a worksheet code module as instructed?

--
HTH

Bob Phillips

"Carol49" wrote in message
...


"Carol49" wrote:

Need formula that will allow a number e.g. 5 to be placed in a cell,
which
t\will then insert 5 rows. Thanks
Formula is what I am looking for, but I am getting a syntax error in
the
line
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
I have copied and pasted, should I be doing something else? Thanks


Yes, I am right clicking on sheet tab and clicking on View Code and then
pasting in formula. Thgat line always comes up red and when I go to enter
in the appropriate cells on the correct sheet I gfet the Compile Error -
Syntax Error. Thanks

"Bob Phillips" wrote:

I am confused, it is fine here.

You say you pasted the formula. What exactly have you copied in?

--
HTH

Bob Phillips

"Carol49" wrote in message
...


"Bob Phillips" wrote:

Have you inserted it in a worksheet code module as instructed?

--
HTH

Bob Phillips

"Carol49" wrote in message
...


"Carol49" wrote:

Need formula that will allow a number e.g. 5 to be placed in a cell,
which
t\will then insert 5 rows. Thanks
Formula is what I am looking for, but I am getting a syntax error in
the
line
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
I have copied and pasted, should I be doing something else? Thanks


Yes, I am right clicking on sheet tab and clicking on View Code and then
pasting in formula. Thgat line always comes up red and when I go to enter
in the appropriate cells on the correct sheet I gfet the Compile Error -
Syntax Error. Thanks


I have tried from Private Worksheet.....
also from On Error Go....
and I stop after End Sub
Thanks