What is worng with this code?

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

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
 
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
 
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
 
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 -
Click to expand...

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