Sort halted

[Gotroots]

I am puzzled why the following code stops when it reaches
Range("B11:Z7800").Select

Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm"
Sheets("A").Select
Range("B11:Z7800").Select
ActiveWorkbook.Worksheets("A").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A").Sort.SortFields.Add
Key:=Range("B11:B7800" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("A").Sort
.SetRange Range("B11:Z7800")

Anyone see something not quite right with the code.

Thank you for your time.

 

[Ryan H]

I'm able to select it just fine. The code works for me. What is description
of the error you are getting?

 

[Gotroots]

Run-time error '1004':
Select method of Range class failed

Would it be anything to do with the fact the workbook is slow to open and
the code proceeds prematurely to the next line in the code.

The workbook is not particularly large 1.17MB although there are upwards of
100,000 array formulas contained in the workbook.

 

[Ryan H]

Try using this:

Workbooks.Open Filename:="Z:\Excel docs\Uses\Uses_A.xlsm"

With ActiveWorkbook.Sheets("A")
.Activate
.Range("B11:Z7800").Select
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("B11:B7800"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.Sort
.SetRange Range("B11:Z7800")
End With

Hope this helps! If so, let me know, click "YES" below.

 

[Gotroots]

Sorry to report but it has stopped at:
..Activate

 

[Dave Peterson]

I'd use the old xl2003 syntax (still supported in xl2007):

dim wkbk as workbook
dim wks as worksheet

set wkbk = Workbooks.Open(Filename:="Z:\Excel docs\Uses\Uses_A.xlsm")
set wks = wkbk.worksheets("A")

with wks
with .range("B11:z7800")
.sort key1:=.columns(1), order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
end with
end with

=============
I guessed that your data had headers in row 11.

I see by your code that you're not including column A in the sort range.

If you meant that, then ignore this. But if you wanted to include column A and
sort by column B, you could use:

with wks
with .range("a11:z7800")
.sort key1:=.columns(2), order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
end with
end with

 

[Gotroots]

I am pleased to say your solution was successful.

I will need to tweak it a bit though.

Thank you
Gotroots

 

[Gotroots]

Ryan

I stated .Activate in fact it should have been .Sort

Anyway the solution Dave Peterson provided was successful.

Thank you Ryan, I appreciate your effort.