Sort without Selecting
 

My current code for Sorting selects the sheet, sorts the data then
returns back to my Data Entry sheet. How can I have this perform this
without the code selecting the sheet? I just want it to happen in the
background without the sheets flashing back and forth while this
happens.

I did a search and found some suggestions but can't find one that works
for my application.

Thanks Jody

' SortcpModels
Sheets("cpModels").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Data Entry").Select
Range("B3").Select

Sort without Selecting
 

Jody,

Try

Sheets("cpModels").Range("A:B").Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"ssjody" wrote in message
oups.com...
My current code for Sorting selects the sheet, sorts the data
then
returns back to my Data Entry sheet. How can I have this
perform this
without the code selecting the sheet? I just want it to happen
in the
background without the sheets flashing back and forth while
this
happens.

I did a search and found some suggestions but can't find one
that works
for my application.

Thanks Jody

' SortcpModels
Sheets("cpModels").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Data Entry").Select
Range("B3").Select

Sort without Selecting
 

Works! Thanks Chip

Jody

Sort without Selecting
 

Hi, Jody,

Thjere are lots of ways you can sort more efficiently, without selecting
forst:

Sub Macro5()
Range("B11:T17").Sort Key1:=Range("B12"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I find it easier to give range names to header cells in your database, so,
id you insert or delete rows, your code always points to the same cell.

In the above example, you could replays Range("B12") with
Range("SalesColHeader") for example, where SalesColHeader is a name applied
to cell B12.

I always call the first field header "DatabaseStart", thus the above example
would begin:
Range("DatabaseStart").Sort Key1

Hope this is of some use

Happy Christmas

Pete



"ssjody" wrote:

My current code for Sorting selects the sheet, sorts the data then
returns back to my Data Entry sheet. How can I have this perform this
without the code selecting the sheet? I just want it to happen in the
background without the sheets flashing back and forth while this
happens.

I did a search and found some suggestions but can't find one that works
for my application.

Thanks Jody

' SortcpModels
Sheets("cpModels").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Data Entry").Select
Range("B3").Select

Sort without Selecting
 

Thanks Pete! That makes good sense. I can use that.

Jody

Sort without Selecting
 

I think this is my current "standard" for sorting.

' SortcpModels
with Sheets("cpModels")
with .range("a:b")
.sort key1:=.columns(1), order1:=xlascending, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with
end with

I like that I can use .columns(1) to specify the key(s).

ssjody wrote:

My current code for Sorting selects the sheet, sorts the data then
returns back to my Data Entry sheet. How can I have this perform this
without the code selecting the sheet? I just want it to happen in the
background without the sheets flashing back and forth while this
happens.

I did a search and found some suggestions but can't find one that works
for my application.

Thanks Jody

' SortcpModels
Sheets("cpModels").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Data Entry").Select
Range("B3").Select

--

Dave Peterson

Sort without Selecting
 

There is a bug in my code. It should be

Sheets("cpModels").Range("A:B").Sort
Key1:=Sheets("cpModels").Range("A2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Chip Pearson" wrote in message
...
Jody,

Try

Sheets("cpModels").Range("A:B").Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"ssjody" wrote in message
oups.com...
My current code for Sorting selects the sheet, sorts the data
then
returns back to my Data Entry sheet. How can I have this
perform this
without the code selecting the sheet? I just want it to happen
in the
background without the sheets flashing back and forth while
this
happens.

I did a search and found some suggestions but can't find one
that works
for my application.

Thanks Jody

' SortcpModels
Sheets("cpModels").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Data Entry").Select
Range("B3").Select