Problem copying name range

  • Thread starter Thread starter IanC
  • Start date Start date
I

IanC

I have the following section of code which is intended to copy a named range
to A4 if the content of C3 is anything except "TWAIN i/f". Didapikit is a
named range (1 row, 5 columns) on another sheet in the workbook and if I
place the line elsewhere in the code (ie not in this subroutine) it will
copy the range.

The worksheet is protected at the point of entry into this subroutine, but
it doesn't make any difference if I uncomment the protect/unprotect lines.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
' Worksheets("Dental").Unprotect
On Error GoTo sub_exit
If Not Intersect(Target, Worksheets("Dental").Range("C3")) Is Nothing
Then
With Target
If .Value = "TWAIN i/f" Then
With Range("A4:E4")
.ClearContents '
.ClearComments
.Borders.LineStyle = xlNone
.UnMerge
.Locked = True
End With
Else
Didapikit.Copy (Worksheets("Dental").Range("A4"))
End If
End With
End If
sub_exit:
Application.EnableEvents = True
' Worksheets("Dental").Protect
End Sub

Basically, the "If" works, but the "Else" doesn't. Any ideas?
 
The worksheet is protected at the point of entry into this subroutine, but
it doesn't make any difference if I uncomment the protect/unprotect lines.
Click to expand...

Sorry, I was wrong about this. If the sheet is protected, the "If" doesn't
work either. The fact remains that the "copy" line doesn't work whether the
sheet is protected or not.
 
Remember that the name of a range and a range variable are two different
things. Your code will work if Didapikit is a genuine range:

Dim Didapikit as Range
Set Didapikit=Range("A1:Z100")
Didapikit.Copy some_place_else


If, however, Didapikit is a Defined Name, then

Range("Didapikit").Copy some_where_else

should be used.
 
Sorted thanks. The subroutine didn't recognise the named range so I had to
"Set" it at the beginning of the routine.
 
Back
Top