By "did not work" I mean that entering a number in the cell A2 had no impact
on the worksheet" - it failed to insert a new row.

"Gord Dibben" wrote:

Define "did not work"


Gord Dibben MS Excel MVP

On Thu, 20 May 2010 09:23:01 -0700, Bilbo Baggs
wrote:

Mike,

Thank you for that prompt response. I was going to wait until the morning
and hope for the best. It worked a treat but because I failed to explain the
whole issue I have created another problem for myself. The instruction was
going to be part of a larger marco that would work on a protected worksheet
that in addition to creating the new row would copy the cell formatting and
formulae from the row above for columns A-N and then clear the entries in
columns A-E of the new row, finishing off by protecting the worksheet again?

I tried including the line 'ActiveSheet.Unprotect' in your set of
instructions before the line where the new row is created but this did not
work. Any advice?

Thank you.

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and try entering a
row number in a2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Rows(Target.Value).EntireRow.Insert
Application.EnableEvents = True
End If
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Bilbo Baggs" wrote:

Hi,

I need to be able to insert a new row into a worksheet at row 'x' where 'x'
is determined by the value in a particular cell. So e.g. if the user enters
the number '34' into cell A2 I want to be able to wite a short macro that
will when run insert a new row into the worksheet at row 34. I just cannot
find a way to automate the process of selecting the require row number?

Strugling with this for hours - would appreciate any help. This is my first
ever online question. Hope it makes sense.

Thank you.

.