Touble between sheets

[Bob]

I have written two routines, each for a different sheet. Both work as
individual routines, however, when I cal the one routine from the other
routine, I get error message 1004
Application-defined Object-defined Error.

I think the problem has to do with cells or ranges being selected on
each sheet and I have having problems getting the program to recognize
the activesheet.

Can someone Help ??
See routine(s) Range1Update and ExtractSumCode
***********************************************************************************************************
Sub ExtractSumCode()

'Purpose: ExtractCustCode() procedure extracts the related Customer
code from
'the Reference worksheet tables

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

End Sub

 

[Karim Benabd]

Hello,

You should add a ".select" as follow:

With Worksheets("AR_Aging").
.Select
.Range("A2").Select
RCount = Range(.Offset(0, 0), .End(xlDown)).Count
ActiveSheet.Range("A1").Select
End With

I hope this helps.

Regards,
Karim Benabd

 

[Bob Phillips]

I have tried to cut out all the selects.

see if this works

Sub ExtractSumCode()
'Purpose: ExtractCustCode() procedure extracts the
'related Customer code from the Reference worksheet tables
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("A2").Range(.Offset(0, 0), .End(xlDown)).Count
With .Range("M2")
.Formula = "=VlookUp(A2,Range1,3)"
.Copy .Range(.Offset(1, 0), .Offset(RCount - 1, 0))
End With
With .Range("M1")
.Value = "SumCode"
.Font.Bold = True
End With
.Range("P1").Range(.Offset(1, 0), .Range("M1").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"
.Range(.End(xlToRight), .End(xlDown)).Sort _
Key1:=Range("D1"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End With

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob]

Thanks for the input, but I did not get the code to work.

It still has a problem switching between sheets

 

[Bob]

Thanks for the input, but I still could not get the code to work.

It still seems the program has a problem switching between sheets.

My code works fine for the specific routine, However, the problem comes
into play when one routine calls the other and then switches back to
the original routine. It runs okay, IF you pause to unselect any
highlighted areas.