What is worng with this code?

[jlclyde]

This code keeps throwing errors. The If statement is in yellow when
the VB window comes up. I have tried putting other code in thre and
it seems to work fine. I do nto knwo if there is an issue due to
adding columns.


Please help.
Thanks,
Jay

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address = "$I$2" And Target.Value = "Inner Box Base"
Then
Columns("K:L").Insert Shift:=xlToRight
Columns("I:J").Copy Destination:=Columns("K:L")
Range("K2") = "Inner Box Lid"
Else
Exit Sub
End If
End Sub

 

[Don Guillett]

Copy to a range instead. Suggest you limit the source to a range instead of
the entire column. If you want this for ALL worksheets then put in the
ThisWorkbook module. Else put in the sheet module of the sheet desired and
change to

Private Sub Worksheet_Change(ByVal Target As Range)
code here
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$I$2" And Target.Value = "Inner Box Base" Then
Columns("K:L").Insert Shift:=xlToRight
Columns("I:J").Copy range("k1")
Range("K2") = "Inner Box Lid"
End If
End Sub

 

[Duke Carey]

try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$2" And Target.Value = "Inner Box Base" Then
Application.EnableEvents = False
Columns("K:L").Insert Shift:=xlToRight
Columns("I:J").Copy Destination:=Columns("K:L")
Range("K2") = "Inner Box Lid"
Application.EnableEvents = True
Else
Exit Sub
End If

End Sub

 

[jlclyde]

Copy to a range instead. Suggest you limit the source to a range instead of
the entire column. If you want this for ALL worksheets then put in the
ThisWorkbook module. Else put in the sheet module of the sheet desired and
change to

Private Sub Worksheet_Change(ByVal Target As Range)
code here
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
 If Target.Address = "$I$2" And Target.Value = "Inner Box Base" Then
  Columns("K:L").Insert Shift:=xlToRight
  Columns("I:J").Copy range("k1")
  Range("K2") = "Inner Box Lid"
End If
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- Show quoted text -

Both of these soutions work, but Don's was a little less typing.
thanks for the fast responce.
Jay