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

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" <jbprather@fuse.net> wrote in message
news:1135093832.451483.321820@g47g2000cwa.googlegroups.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
>

Works! Thanks Chip

Jody

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
>
>

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

Jody

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

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" <chip@cpearson.com> wrote in message
news:OlBjW%23XBGHA.3604@TK2MSFTNGP09.phx.gbl...
> 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" <jbprather@fuse.net> wrote in message
> news:1135093832.451483.321820@g47g2000cwa.googlegroups.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
>>
>
>

Thanks, this was super helpful! I had some linked sheets and the data needed to be sorted properly in the Data sheet in order for the rest of them to work, this macro applied to all those tabs made the file much easier to hand off.