ClearContents terminating macro - using 2 workbooks

[Orbitboy]

Hello...

The objective for the macro is to clear a named range in workbook 2 (it is a
range of static text values used for lookup purposes) then copy the updated
range from workbook 1, redefine the Named range in workbook 2 and then save
and close workbook 2. The macros is called from a button located in workbook
1.

The codes runs fine until the .Range("Name").ClearContents is executed. It
completes the instruction and the causes the macro code to stop/end abruptly
without an error message. The code is listed below:

Sub SKULabel_EXTUpdate()
Dim ws1, ws2 As Worksheet ' worksheet variables
Dim wkbk1, wkbk2 As Workbook ' workbook variables
Dim rng As Range ' range variable
Dim strPath As String ' variable for file path name
Dim strNamedAddress As String ' variable for Named Range address

' Reset dimensions on named range used for exporting to static lookup file
AssignEXTDATA_RangeName - PROCEDURE CALL THAT EXECUTES IN WORKBOOK 1
WITHOUT ANY PROBLEM

Set wkbk1 = ActiveWorkbook
Set ws1 = wkbk1.Worksheets("SKU_Listing")
strPath = Worksheets("Lookups").Range("B2").Value
strPath = strPath & "ABCSKUListing.xls"

' Open static lookup file and initialize for updated SKU Listing
Set wkbk2 = Workbooks.Open(Filename:=strPath)
Set ws2 = wkbk2.Worksheets("SKU_Line_Labels")

<----- MACRO ENDS RIGHT AFTER THIS LINE ------->
ws2.Range("SKULineLabels").ClearContents

On Error Resume Next - DEBUGGING ATTEMPT
MsgBox "Here" - DEBUGGING ATTEMPT
<---- THE FOLLOWING LINES ARE DIFFERENT APPROACHES I TRIED TO RESOLVE THIS
ISSUE - ALL TO NO AVAIL. ---->
' With ws2
' .Range("A1").Activate
' .Range("SKULineLabels").ClearContents
' End With
' ws2.Activate
' ws2.Range("SKULineLabels").ClearContents
' Range("SKULineLabels").ClearContents
' Selection.Delete

ws1.Activate
' Copy updated SKU Listing from Master SKU List in ABC Input Pricing file
ws1.Range("SKULineLabels").Copy
ws2.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
' Redefine named range of the SKU Listing in the static lookup file,
' then Save and Close the ABCSKUListing.xls Workbook
strNamedAddress = "=" & ActiveSheet.Name & "!" & Selection.Address
ws2.Names.Add Name:="SKULineLabels", RefersTo:=strNamedAddress
ws2.Range("A1").Select
wkbk2.Close Savechanges:=True
ws1.Activate
ws1.Range("I3").Select
MsgBox "SKU Listing has been updated."

End Sub ' SKULabel_EXTUpdate

Have been searching the newsgroups and other Excel sites for some insight...
am quickly losing my mind on this one. Running Excel 2003 on XP SP2.

Thanks in advance.

 

[Alan]

I tested the part of the code that you say is in error but it works fine for
me. Check that range name SKULineLabels does exist in
Worksheets("SKU_Line_Labels").

Alan

 

[Orbitboy]

Thanks for your reply Alan... the named range does exist because the
..ClearContents line executes and clears the range... that is when the macro
litterly "resets". If I use the step into command on the debugger toolbar to
execute the next line the macro starts again from the beginnning. Making no
sense to me... any additional insight is greatly appreciated.