Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying name range
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? -- Ian -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying name 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. 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. -- Ian -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying name range
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. -- Gary''s Student - gsnu200905 "IanC" wrote: 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? -- Ian -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying name range
Sorted thanks. The subroutine didn't recognise the named range so I had to
"Set" it at the beginning of the routine. -- Ian -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie: VBA problem when copying cell range | Excel Programming | |||
Problem copying a range to another file | Excel Programming | |||
copying and pasting range problem | Excel Programming | |||
Problem copying formula to range of cells | Setting up and Configuration of Excel | |||
Problem copying a range to a different workbook | Excel Programming |