Sort Problem

I currently have the code listed below

Windows("Call List.xls").activate
Sheets("Call List").Select

Columns("A:D").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers

Thanks to the help of this list I am getting away from the activate and select statements, but I have been unsuccessful at getting this one rewritten. Using the set statement below how would I do the sort listed in the code above.

Set wsCallList = Workbooks("Call List.xls").Sheets("Call List")

Thanks for the help!

Jeff

mudraker,

I have tried what you have listed, but it keeps throwing me into my error handler. I get Error #1004.

Jeff

Every appears to be spelled correctly and I have declared wsCallList as a Worksheet. Below is the code I am using in the subroutine.

Option Explicit
Dim sAreaCode As String
Dim sPhoneNumber As String
Dim sFullNumber As String
Dim sOldFullNumber As String
Dim sTimeZone As String
Dim sState As String
Dim lDataRow As Long
Dim lErrorRow As Long
Dim lTargetRow As Long
Dim lLastRow As Long
Dim bWBOpen As Boolean
Dim wsCallData As Worksheet
Dim wsCallList As Worksheet
Dim wsCallErrors As Worksheet
Dim wsAreaCodes As Worksheet
Dim rAreaCodes As Range

Sub JoinNumbers()

bWBOpen = WorkbookIsOpen("Area Code List.xls")
If bWBOpen <> True Then
    Workbooks.Open Filename:="C:\John\Area Code List.xls"
End If

lDataRow = 2
lErrorRow = 1
lTargetRow = 2

Set wsCallData = Workbooks("Call List.xls").Sheets("Data")
Set wsCallList = Workbooks("Call List.xls").Sheets("Call List")
Set wsCallErrors = Workbooks("Call List.xls").Sheets("Errors")
Set wsAreaCodes = Workbooks("Area Code List.xls").Sheets("List")

wsCallData.Columns("A:C").NumberFormat = "@"

sAreaCode = wsCallData.Cells(lDataRow, 1)
sPhoneNumber = wsCallData.Cells(lDataRow, 2)

Application.ScreenUpdating = False
Do While Len(sAreaCode) <> 0
    
    On Error GoTo ErrorHandler
    
    sFullNumber = sAreaCode + sPhoneNumber

    Windows("Area Code List.xls").Activate
    Sheets("List").Select
    Range("A1").Select
    
    Set rAreaCodes = wsAreaCodes.Cells.Find(What:=sAreaCode, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

    If Not rAreaCodes Is Nothing Then
        sState = rAreaCodes.Offset(0, 1).Value
        sTimeZone = rAreaCodes.Offset(0, 3).Value
    Else
        wsCallErrors.Cells(lErrorRow, 1) = sAreaCode
        wsCallErrors.Cells(lErrorRow, 2) = sFullNumber
        wsCallErrors.Cells(lErrorRow, 3) = "Area Code not found in list"
        lErrorRow = lErrorRow + 1
        GoTo 50
    End If
    
    wsCallList.Cells(lTargetRow, 1) = sFullNumber
    wsCallList.Cells(lTargetRow, 2) = sState
    wsCallList.Cells(lTargetRow, 3) = sTimeZone
    wsCallList.Cells(lTargetRow, 4) = lTargetRow
    lTargetRow = lTargetRow + 1
    
50  lDataRow = lDataRow + 1
    
    sAreaCode = wsCallData.Cells(lDataRow, 1)
    sPhoneNumber = wsCallData.Cells(lDataRow, 2)

Loop
Application.ScreenUpdating = True

Windows("Area Code List.xls").Close

wsCallList.Columns("A:D").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers



Set wsCallData = Nothing
Set wsCallList = Nothing
Set wsCallErrors = Nothing
Set wsAreaCodes = Nothing

Exit Sub        ' Exit to avoid handler.
ErrorHandler:    ' Error-handling routine.
    
End Sub

Thanks for the help!

Jeff