Thanks for all your help, the dots almost sent me dotty!

The working code i am now using is:

Private Sub CommandButton2_Click()
Do Until [b11] = 0
If [b11] < 0 Then
MsgBox prompt:="Error detected, click ok to attempt a sort fix",
Buttons:=vbExclamation
With Worksheets("Existing")
Set rgLastEx = .Range("A1").CurrentRegion
rgLastEx.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess
MsgBox prompt:="Existing Sorted"
End With
With Worksheets("Options")
Set rgLastOp = .Range("A1").CurrentRegion
rgLastOp.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess
MsgBox prompt:="Options Sorted"
End With
Else: MsgBox prompt:="No errors have been detected, please continue",
Buttons:=vbInformation
End If
Loop
End Sub

I just need to move the no errors message now.

Thanks again
Ian

"Tom Ogilvy" wrote:

if you use the code you show in the click event of a commandbutton on sheet1
(as an example) it will perform the sort on sheet1. If you wanted it to
sort data on sheet2 you would need

Private Sub CommandButton1_Click()
Dim rgLastEx as Range
with Worksheets("Sheet2")
set rgLastEx = .Range("A1:R37775")
rgLastEx.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess
End With
End Sub

If the data is set up like a data base (no embedded entirely blank rows or
columns), to dynamically pick up the data

Private Sub CommandButton1_Click()
Dim rgLastEx as Range
with Worksheets("Sheet2")
set rgLastEx = .Range("A1").CurrentRegion
rgLastEx.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess
End With
End Sub

In fact, the above code is copied from the code module for Sheet1 where it
is working very well.

If I remove the "." from the front of each range, the sort occurs on Sheet1.

If I leave the "." before .Range("A1").CurrentRegion and remove it from
Key1:=Range("A1")
then I get your error.

Post your code.

--
Regards,
Tom Ogilvy


"Ian_Limbo" wrote in message
...
I've put the code in but it still bugs out on the sort line, with this
error:

1004
The sort ref is not valid. Make sure that its within the data you want to
sort, and the first sort box isn't the same or blank.

If i manually type in the data range as:
set rgLastEx = Range("A1:R37775")
and the sort line as:
rgLastEx.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
then it will sort the data

I have tried Sharad's fix as well with the same problem.

any ideas?

"Tom Ogilvy" wrote:

sorry, didn't read your first posting.

Private Sub CommandButton2_Click()
Do Until [b11] = 0
With Sheets("Existing")
Set rgLastEx = .Range("A1").SpecialCells_(xlCellTypeLastCell)
MsgBox rgLastEx
End With
With Sheets("Options")
Set rgLastOp = .Range("A1").SpecialCells_(xlCellTypeLastCell)
End With
If [b11] < 0 Then
MsgBox prompt:="Error detected, click ok to attempt a sort fix",
_
Buttons:=vbExclamation
With Sheets("Existing")
.Range("A1").currentRegion..Sort _
Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess
End With
MsgBox prompt:="Existing Sorted"
Else
' This will never be shown.
MsgBox prompt:="No errors have been detected, please continue", _
Buttons:=vbInformation
End If
Loop
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Your only sorting one cell.
Dim lastRow as long

With Sheets("Existing")
lastRow = .cells(rows.count,1).End(xlup).Row
MsgBox rgLastEx
MsgBox prompt:="Error detected, click ok to attempt a sort
fix",
_
Buttons:=vbExclamation
.Range(.Range("A1"),.Range("A" & lastRow).Sort _
Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess
MsgBox prompt:="Existing Sorted"
End With

--
Regards,
Tom Ogilvy


"Ian_Limbo" wrote in message
...
I've changed all this items, and i've also tried putting it all into
With
statements, but it still won't sort the data. It appears that it
does
not
like my range, possibly!

The new code i'm trying is:

With Sheets("Existing")
Set rgLastEx = .Range("A1").SpecialCells(xlCellTypeLastCell)
MsgBox rgLastEx
MsgBox prompt:="Error detected, click ok to attempt a sort
fix",
Buttons:=vbExclamation
rgLastEx.Sort Key1:=Sheets("Existing").Range("A1"),
Order1:=xlAscending, Header:=xlGuess
MsgBox prompt:="Existing Sorted"
End With

"Sharad Naik" wrote:

As an immediate observervation :-
Change all Range("A1") inside between With and End With to
.Range("A1") (a dot before R).

Then for the 3rd Range("A1") which is not within a With para,
decide which sheet it refers to and change it to
Sheets("That-Sheet-Name").Range("A1")

Sharad

"Ian_Limbo" wrote in message
...
I have a list with a fixed no. of columns and a variable no. of
rows.
I
need
to sort this list using a button on another sheet. I am not
sure if
the
error is in selecting the range or in my sort code. The code I
have
so
far
is:

Private Sub CommandButton2_Click()
Do Until [b11] = 0
With Sheets("Existing")
Set rgLastEx =
Range("A1").SpecialCells_(xlCellTypeLastCell)
MsgBox rgLastEx
End With
With Sheets("Options")
Set rgLastOp =
Range("A1").SpecialCells_(xlCellTypeLastCell)
End With
If [b11] < 0 Then
MsgBox prompt:="Error detected, click ok to attempt a
sort
fix",
Buttons:=vbExclamation
rgLastEx.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess
MsgBox prompt:="Existing Sorted"
Else: MsgBox prompt:="No errors have been detected, please
continue",
Buttons:=vbInformation
End If
Loop
End Sub


Many thanks for any help

Ian