Sorting a range on a sheet you aren't on 1

[Asspin]

Ok... I am trying to sort a range that is on another page then the one I am on. Basically I am using it in a dropdown, and need to sort it after I have added a value to the list. Everything works except for the sorting. Any ideas?

Here is what I am trying. I get the error on the highlighted line, with the following error.

Run-time error '1004':
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't that same or blank.

Option Explicit
Public bLChange As Boolean, bFChange As Boolean, bFirst As Boolean, bLast As Boolean

Private Sub cmdAddUser_Click()
Dim sUser As String, sMsg As String, c As Range, fCell As Range
    Application.ScreenUpdating = False
    sUser = Trim(txtFName.Text) & " " & Trim(txtLName.Text)
    If optMgt.Value = False Then
        If Not Vars.Range("E93").Value = "" Then
            Application.ScreenUpdating = True
            sMsg = MsgBox("The userlist is full, please delete some users!", vbCritical + vbOKOnly + vbDefaultButton1, "FCC SaM")
            Exit Sub
        Else
        For Each c In Sheets("Variables").Range("E3:E93")
            If c.Value = sUser Then
                sMsg = MsgBox("That name already exists in the userlist!", vbCritical + vbOKOnly + vbDefaultButton1, "FCC SaM")
                Exit Sub
            End If
            If c.Value = "" Then Exit For
        Next
            Vars.Range("E93").Value = sUser
            [highlight]Vars.Range("E3:E93").Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal[/highlight]
        End If
    Else
        With Vars.Columns("F")
            Set fCell = .Find(comMgrTeam.Value, LookIn:=xlValues, LookAt:=xlWhole)
        End With
        Vars.Range(fCell.Offset(0, 1), fCell.Offset(0, 1)).Value = sUser
    End If
        txtFName.Value = ""
        txtLName.Value = ""
        comMgrTeam.Value = ""
        optRep.Value = True
        Call UserForm_Initialize
    Application.ScreenUpdating = True
End Sub

Dan

 

[PHV]

Key1:=Vars.Range("E3"),

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

What is Vars defined as? It better be a Worksheet.

With Vars
  .Range("E3:E93").Sort _
  Key1:=[red][b].[/b][/red]Range("E3"), _
  Order1:=xlAscending, _
  Header:=xlNo, _
  OrderCustom:=1, _
  MatchCase:=False, _
  Orientation:=xlTopToBottom, _
  DataOption1:=xlSortNormal
end with

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Asspin]

As always, a perfect solution.

Dan

 

[Asspin]

Yes, Vars is a sheet name. Makes more sence then Sheet3.

Dan