I think this is ridiculous

I have a worksheet with as many as 100 lrows of info that track shipments. If
i mark an "N" in column G a second row for that shipping numbers is rquired.
My boss wants me to make that automated. So the row will be automatically
inserted. Any help is appreciated.

Thanks,
Steve

Right click on the sheet tab and select view code.

In the resulting sheet module, at the top of the module are two dropdowns.
In the left dropdown select Worksheet and in the right dropdown select
Change (not SelectionChange)

You should get a sub declaration like this

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


You can put your code there. Use code like this (you can paste in this code
over the declaration if you wish)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Target.Column <> 7 Then Exit Sub ' change made in column G
If Target.Count > 1 Then Exit Sub
If Target.Value = "N" Then
Application.EnableEvents = False
Target.Offset(1, 0).EntireRow.Insert
End If
ErrHandler:
Application.EnableEvents = True
End Sub


--
Regards,
Tom Ogilvy



"Steve" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet with as many as 100 lrows of info that track shipments.
If
> i mark an "N" in column G a second row for that shipping numbers is
rquired.
> My boss wants me to make that automated. So the row will be automatically
> inserted. Any help is appreciated.
>
> Thanks,
> Steve

Paste this code into the worksheet. Right click the tab -> Select View Code
-> paste this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Count = 1 Then
If Target.Column = 7 And Target.Value = "N" Or _
Target.Value = "n" Then _
Target.Offset(1, 0).EntireRow.Insert xlDown
End If
End Sub

--
HTH...

Jim Thomlinson


"Steve" wrote:

> I have a worksheet with as many as 100 lrows of info that track shipments. If
> i mark an "N" in column G a second row for that shipping numbers is rquired.
> My boss wants me to make that automated. So the row will be automatically
> inserted. Any help is appreciated.
>
> Thanks,
> Steve

Sorry... This code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Count = 1 Then
If Target.Column = 7 And UCase(Target.Value) = "N" Then _
Target.Offset(1, 0).EntireRow.Insert xlDown
End If
End Sub
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Paste this code into the worksheet. Right click the tab -> Select View Code
> -> paste this
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error Resume Next
> If Target.Count = 1 Then
> If Target.Column = 7 And Target.Value = "N" Or _
> Target.Value = "n" Then _
> Target.Offset(1, 0).EntireRow.Insert xlDown
> End If
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Steve" wrote:
>
> > I have a worksheet with as many as 100 lrows of info that track shipments. If
> > i mark an "N" in column G a second row for that shipping numbers is rquired.
> > My boss wants me to make that automated. So the row will be automatically
> > inserted. Any help is appreciated.
> >
> > Thanks,
> > Steve

Steve

Right click on the sheet tab and choose view code. Paste this code in the
code window

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = Me.Columns("G").Column And Target.Value = "N" Then
Target.Offset(1, 0).EntireRow.Insert
Me.Cells(Target.Offset(1, 0).Row, 1).Value = Me.Cells(Target.Row,
1).Value
End If

Application.EnableEvents = True

End Sub

This inserts a row below the row where the N was entered and copies the
value from column A to the next row (assumes col A is a unique identifier
that you want on the next row)


--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Steve wrote:
> I have a worksheet with as many as 100 lrows of info that track
> shipments. If i mark an "N" in column G a second row for that
> shipping numbers is rquired. My boss wants me to make that automated.
> So the row will be automatically inserted. Any help is appreciated.
>
> Thanks,
> Steve