lawrenb
05-31-2006, 09:15 AM
The two Subs ( ExtractSumCode & Range1Update) each work as separate routines. However, when I call one from the other, I get "error 1004, Application-defined Object-defined error"
Each time I get the error, if I go back to the file and click off the selected range/cells, Then the Sub will run successfully again............as a separate routine.
Can someone help me with the code to get the program to switch between sheets.
******************************************************
Sub ExtractSumCode()
'Purpose: ExtractCustCode() procedure extracts the related Customer code from
'the Reference worksheet tables using IF conditions and VLOOKUP functions.
Dim i As Integer
Dim RCount As Long
Dim SumCode As Range, TestTable As Range
Dim cell As Range
Call Worksheets("Reference").Range1Update
With Worksheets("AR_Aging").Range("a2")
RCount = Range(.Offset(0, 0), .End(xlDown)).Count
ActiveSheet.Range("A1").Select
End With
With Worksheets("AR_Aging").Range("M2")
.Formula = "=VlookUp(A2,Range1,3)"
End With
With Worksheets("AR_Aging").Range("M2")
.Copy
Range(.Offset(1, 0), .Offset(RCount - 1, 0)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With
With Worksheets("AR_Aging").Range("M1")
.Value = "SumCode"
.Font.Bold = True
Range("P1").Range(.Offset(1, 0), .End(xlDown)).Name = "SumCode"
End With
End Sub
********************************************************
Sub Range1Update()
'This routine updates the range "Range1" to provide an effective table for customer SumCode
ActiveWorkbook.Names("Range1").Delete
With Worksheets("Reference").Range("D1")
Range(.Offset(0, 0), .Offset(0, 2).End(xlDown)).Name = "Range1"
ActiveSheet.Range("A1").Select
End With
With Worksheets("Reference").Range("D1")
ActiveSheet.Range(.End(xlToRight), .End(xlDown)).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End With
With Worksheets("AR_Aging").Range("A65300")
End With
Each time I get the error, if I go back to the file and click off the selected range/cells, Then the Sub will run successfully again............as a separate routine.
Can someone help me with the code to get the program to switch between sheets.
******************************************************
Sub ExtractSumCode()
'Purpose: ExtractCustCode() procedure extracts the related Customer code from
'the Reference worksheet tables using IF conditions and VLOOKUP functions.
Dim i As Integer
Dim RCount As Long
Dim SumCode As Range, TestTable As Range
Dim cell As Range
Call Worksheets("Reference").Range1Update
With Worksheets("AR_Aging").Range("a2")
RCount = Range(.Offset(0, 0), .End(xlDown)).Count
ActiveSheet.Range("A1").Select
End With
With Worksheets("AR_Aging").Range("M2")
.Formula = "=VlookUp(A2,Range1,3)"
End With
With Worksheets("AR_Aging").Range("M2")
.Copy
Range(.Offset(1, 0), .Offset(RCount - 1, 0)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With
With Worksheets("AR_Aging").Range("M1")
.Value = "SumCode"
.Font.Bold = True
Range("P1").Range(.Offset(1, 0), .End(xlDown)).Name = "SumCode"
End With
End Sub
********************************************************
Sub Range1Update()
'This routine updates the range "Range1" to provide an effective table for customer SumCode
ActiveWorkbook.Names("Range1").Delete
With Worksheets("Reference").Range("D1")
Range(.Offset(0, 0), .Offset(0, 2).End(xlDown)).Name = "Range1"
ActiveSheet.Range("A1").Select
End With
With Worksheets("Reference").Range("D1")
ActiveSheet.Range(.End(xlToRight), .End(xlDown)).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End With
With Worksheets("AR_Aging").Range("A65300")
End With