Hi Howard,

Am Sat, 7 Sep 2013 01:37:37 -0700 (PDT) schrieb Howard:

This offset only works with Swine which is in col A.
c.Offset(0, 4).Copy Range("J50").End(xlUp).Offset(1, 0)
c.Offset(0, 5).Copy Range("K50").End(xlUp).Offset(1, 0)

Dairy is in col B and the offsets should have been 3 and 4, Beef 2 and 3.

you can do that also a bit easier (enlarge the code - I have only three
items in it):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$H$1" Then Exit Sub

Dim c As Range
Dim myOffset As Integer

Select Case Target.Value
Case "Swine"
myOffset = 4
Case "Diary"
myOffset = 3
Case "Beef"
myOffset = 2
End Select

With Range(Target & "x").SpecialCells(xlCellTypeConstants)
.Copy Range("I50").End(xlUp).Offset(1, 0)
.Offset(columnoffset:=myOffset).Copy _
Range("J50").End(xlUp).Offset(1, 0)
.Offset(columnoffset:=myOffset + 1).Copy _
Range("K50").End(xlUp).Offset(1, 0)
End With
..
..


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2