Problem between sheets

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.
******************************************************
[VBA]
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
[/VBA]

HAHHAH problem between sheets is the best title EVER!

Nice title ! ?? **

Try this..

Its all in the reference sheet code module


[VBA]Option Explicit
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")
RCount = .Range("A65536").End(xlUp).Row
With .Range("M2:M" & Trim(Str(RCount)))
.Formula = "=VlookUp(A2,Range1,3,false)" 'false ensure no xl approx match
.Name = "SumCode"
End With
With .Range("M1") ' Presumed P1 was supposed to be M1 ?
.Value = "SumCode"
.Font.Bold = True
End With
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"
End With
With Worksheets("Reference").Range("D1")
.Sort Key1:=Range("Range1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End With
End Sub
[/VBA]