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.
|
Similar Threads
