Automatically insert row below cell when text is written in.

[nickstar76]

Hey there

I know a little about Macro's but i have not been able to find the details online so far. What I want is for any information that is entered into column J for that to automatically insert a row underneath that row. The main problem i seem to be having is I can find a macro but you obviously need to click on tools>macro>run macro which goes against the whole point of it being automatic. The rows involved are 3:500.

 

[VoG]

Hi. Try this: right click the sheet's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then Target.Offset(1).EntireRow.Insert
End Sub

 

[JamesW]

Hi there,

Welcome to the board.

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
        If Not Intersect(Target, Columns(10)) Is Nothing Then
            Target.Offset(1).EntireRow.Insert
        End If
End Sub

 

[nickstar76]

Hi there

After putting in the codes i get this error

Compile Error:
Ambiguous name detected: Worksheet_Change

This is what is currently in my Visual Basic

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 7)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub

So how would I put in multiple Visual Basic Codes? Is that the problem?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
    With Target(1, 7)
        .Value = Date
        .EntireColumn.AutoFit
    End With
ElseIf Target.Column = 10 Then
    Target.Offset(1).EntireRow.Insert
End If
End Sub

 

[nickstar76]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
    With Target(1, 7)
        .Value = Date
        .EntireColumn.AutoFit
    End With
ElseIf Target.Column = 10 Then
    Target.Offset(1).EntireRow.Insert
End If
End Sub

I still get the same error as above. I have tried your code in another spreadsheet and it works so I am thinking its mixing with the current VBA code that is causing the problem? I thought I would give you the information on the sheet at the moment as well that the first 2 rows currently have headings so row 3 is where the information starts (if that is any help)

 

[VoG]

You need to delete your original code so that you only have one Worksheet_Change sub.

 

[nickstar76]

You need to delete your original code so that you only have one Worksheet_Change sub.

So can I only have one original code because I still need that worksheet change sub?

 

[VoG]

I've incorporated your original plus mine in one sub!

 

[nickstar76]

I've incorporated your original plus mine in one sub!

Wow thanks heaps for that. The only other thing which Im not sure if you are able to fix is that if you click on that cell and write something in it enters a row but if you go to delete it or right something else over the top it enters a row again. Is there anyway you can stop that?