Excel - Auto Sort - Stop going back to the top of the table

up vote
0
down vote

favorite

I am using the below code (which works wonderfully) however once I select someone in the "Associate" column the table automatically sorts the data but it takes me back to the top of the table. I would like to stay on the current row I just completed. I don't want to continually scroll back down. Is that possible?

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields.Clear

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#All],[Time]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

If Not Intersect(Target, Range("Table1[[#All],[Associate]]")) Is Nothing Then

    With ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End If

End Sub

asked Nov 21 at 18:48

KC0904

2

Target.Activate maybe?
– BigBen
Nov 21 at 18:52

3

Inside a sheet code module, Me refers to the sheet itself, so you can use that in place of ThisWorkbook.Sheets("Log")
– Tim Williams
Nov 21 at 19:12

add a comment |

up vote
0
down vote

favorite

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields.Clear

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#All],[Time]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

If Not Intersect(Target, Range("Table1[[#All],[Associate]]")) Is Nothing Then

    With ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End If

End Sub

asked Nov 21 at 18:48

KC0904

2

Target.Activate maybe?
– BigBen
Nov 21 at 18:52

3

Inside a sheet code module, Me refers to the sheet itself, so you can use that in place of ThisWorkbook.Sheets("Log")
– Tim Williams
Nov 21 at 19:12

add a comment |

up vote
0
down vote

favorite

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields.Clear

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#All],[Time]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

If Not Intersect(Target, Range("Table1[[#All],[Associate]]")) Is Nothing Then

    With ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End If

End Sub

asked Nov 21 at 18:48

KC0904

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields.Clear

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort.SortFields. _

    Add Key:=Range("Table1[[#All],[Time]]"), SortOn:=xlSortOnValues, Order _

    :=xlAscending, DataOption:=xlSortNormal

If Not Intersect(Target, Range("Table1[[#All],[Associate]]")) Is Nothing Then

    With ThisWorkbook.Sheets("Log").ListObjects("Table1").Sort

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End If

End Sub

excel vba excel-vba

asked Nov 21 at 18:48

KC0904

asked Nov 21 at 18:48

KC0904

asked Nov 21 at 18:48

KC0904

asked Nov 21 at 18:48

KC0904

asked Nov 21 at 18:48

KC0904

2

Target.Activate maybe?
– BigBen
Nov 21 at 18:52

3

Inside a sheet code module, Me refers to the sheet itself, so you can use that in place of ThisWorkbook.Sheets("Log")
– Tim Williams
Nov 21 at 19:12

add a comment |

2

Target.Activate maybe?
– BigBen
Nov 21 at 18:52

3

Inside a sheet code module, Me refers to the sheet itself, so you can use that in place of ThisWorkbook.Sheets("Log")
– Tim Williams
Nov 21 at 19:12

Target.Activate maybe?
– BigBen
Nov 21 at 18:52

Inside a sheet code module, Me refers to the sheet itself, so you can use that in place of ThisWorkbook.Sheets("Log")
– Tim Williams
Nov 21 at 19:12

add a comment |

1 Answer
1

active

oldest

votes

up vote
1
down vote

Maybe as simple as adding a final Target.Activate.

answered Nov 21 at 18:59

BigBen

4,5652417

Where do I added it? After the End if?
– KC0904
Nov 21 at 20:30

I assume you want before - activate after sorting.
– BigBen
Nov 21 at 20:31

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53418742%2fexcel-auto-sort-stop-going-back-to-the-top-of-the-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
1
down vote

Maybe as simple as adding a final Target.Activate.

answered Nov 21 at 18:59

BigBen

4,5652417

Where do I added it? After the End if?
– KC0904
Nov 21 at 20:30

I assume you want before - activate after sorting.
– BigBen
Nov 21 at 20:31

add a comment |

up vote
1
down vote

Maybe as simple as adding a final Target.Activate.

answered Nov 21 at 18:59

BigBen

4,5652417

Where do I added it? After the End if?
– KC0904
Nov 21 at 20:30

I assume you want before - activate after sorting.
– BigBen
Nov 21 at 20:31

add a comment |

up vote
1
down vote

Maybe as simple as adding a final Target.Activate.

answered Nov 21 at 18:59

BigBen

4,5652417

Maybe as simple as adding a final Target.Activate.

answered Nov 21 at 18:59

BigBen

4,5652417

answered Nov 21 at 18:59

BigBen

4,5652417

answered Nov 21 at 18:59

BigBen

4,5652417

answered Nov 21 at 18:59

BigBen

4,5652417

Where do I added it? After the End if?
– KC0904
Nov 21 at 20:30

I assume you want before - activate after sorting.
– BigBen
Nov 21 at 20:31

add a comment |

Where do I added it? After the End if?
– KC0904
Nov 21 at 20:30

I assume you want before - activate after sorting.
– BigBen
Nov 21 at 20:31

Where do I added it? After the End if?
– KC0904
Nov 21 at 20:30

I assume you want before - activate after sorting.
– BigBen
Nov 21 at 20:31

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Qfyilyi