Excel VBA Auto Sort Once Row Has been completed
up vote
0
down vote
favorite
I have the following code but I am getting the following error:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed
I can't seem to figure this out. The error appears on the If Not Intersect row. Any help would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields. _
Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("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 ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
excel vba excel-vba
asked Nov 21 at 16:31
KC0904
84
add a comment |
up vote
0
down vote
favorite
I have the following code but I am getting the following error:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed
I can't seem to figure this out. The error appears on the If Not Intersect row. Any help would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields. _
Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("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 ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
excel vba excel-vba
asked Nov 21 at 16:31
KC0904
84
1
Works for me. Do you definitely have a column headed "Associate"?
– SJR
Nov 21 at 16:37
1
I'd fully qualify theRange("Table1[[#All],[Associate]]"). Thisworkbook.sheets("Log")? I'd also suggest you use ThisWorkBook instead of ActiveWorkBook. ActiveWorkbook can cause unexpected errors if you swap to another workbook while the macro is running.
– Gravitate
Nov 21 at 16:39
SJR - Yes I have a column header titled Associate.
– KC0904
Nov 21 at 16:41
Thanks Gravitate. After further review my column header had a space at the end of it. Once I removed it the code worked. I also made your recommendations regarding the ThisWorkBook. Thank you for your help.
– KC0904
Nov 21 at 16:47
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
– KC0904
Nov 21 at 18:00
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have the following code but I am getting the following error:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed
I can't seem to figure this out. The error appears on the If Not Intersect row. Any help would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields. _
Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("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 ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
excel vba excel-vba
asked Nov 21 at 16:31
KC0904
84
I have the following code but I am getting the following error:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed
I can't seem to figure this out. The error appears on the If Not Intersect row. Any help would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort.SortFields. _
Add Key:=Range("Table1[[#ALL],[Date]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("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 ActiveWorkbook.Worksheets("Log").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
excel vba excel-vba
excel vba excel-vba
asked Nov 21 at 16:31
KC0904
84
asked Nov 21 at 16:31
KC0904
84
edited Nov 21 at 17:48
asked Nov 21 at 16:31
KC0904
84
asked Nov 21 at 16:31
KC0904
84
asked Nov 21 at 16:31
KC0904
84
84
1
Works for me. Do you definitely have a column headed "Associate"?
– SJR
Nov 21 at 16:37
1
I'd fully qualify theRange("Table1[[#All],[Associate]]"). Thisworkbook.sheets("Log")? I'd also suggest you use ThisWorkBook instead of ActiveWorkBook. ActiveWorkbook can cause unexpected errors if you swap to another workbook while the macro is running.
– Gravitate
Nov 21 at 16:39
SJR - Yes I have a column header titled Associate.
– KC0904
Nov 21 at 16:41
Thanks Gravitate. After further review my column header had a space at the end of it. Once I removed it the code worked. I also made your recommendations regarding the ThisWorkBook. Thank you for your help.
– KC0904
Nov 21 at 16:47
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
– KC0904
Nov 21 at 18:00
add a comment |
1
Works for me. Do you definitely have a column headed "Associate"?
– SJR
Nov 21 at 16:37
1
I'd fully qualify theRange("Table1[[#All],[Associate]]"). Thisworkbook.sheets("Log")? I'd also suggest you use ThisWorkBook instead of ActiveWorkBook. ActiveWorkbook can cause unexpected errors if you swap to another workbook while the macro is running.
– Gravitate
Nov 21 at 16:39
SJR - Yes I have a column header titled Associate.
– KC0904
Nov 21 at 16:41
Thanks Gravitate. After further review my column header had a space at the end of it. Once I removed it the code worked. I also made your recommendations regarding the ThisWorkBook. Thank you for your help.
– KC0904
Nov 21 at 16:47
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
– KC0904
Nov 21 at 18:00
1
1
Works for me. Do you definitely have a column headed "Associate"?
– SJR
Nov 21 at 16:37
Works for me. Do you definitely have a column headed "Associate"?
– SJR
Nov 21 at 16:37
1
1
I'd fully qualify the
Range("Table1[[#All],[Associate]]"). Thisworkbook.sheets("Log")? I'd also suggest you use ThisWorkBook instead of ActiveWorkBook. ActiveWorkbook can cause unexpected errors if you swap to another workbook while the macro is running.– Gravitate
Nov 21 at 16:39
I'd fully qualify the
Range("Table1[[#All],[Associate]]"). Thisworkbook.sheets("Log")? I'd also suggest you use ThisWorkBook instead of ActiveWorkBook. ActiveWorkbook can cause unexpected errors if you swap to another workbook while the macro is running.– Gravitate
Nov 21 at 16:39
SJR - Yes I have a column header titled Associate.
– KC0904
Nov 21 at 16:41
SJR - Yes I have a column header titled Associate.
– KC0904
Nov 21 at 16:41
Thanks Gravitate. After further review my column header had a space at the end of it. Once I removed it the code worked. I also made your recommendations regarding the ThisWorkBook. Thank you for your help.
– KC0904
Nov 21 at 16:47
Thanks Gravitate. After further review my column header had a space at the end of it. Once I removed it the code worked. I also made your recommendations regarding the ThisWorkBook. Thank you for your help.
– KC0904
Nov 21 at 16:47
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
– KC0904
Nov 21 at 18:00
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
– KC0904
Nov 21 at 18:00
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
After reviewing the comments by SJR & Gravitate I rechecked my header column. It appears that there was an extra space at the end which once I removed it the code worked as intended.
answered Nov 21 at 16:57
KC0904
84
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
After reviewing the comments by SJR & Gravitate I rechecked my header column. It appears that there was an extra space at the end which once I removed it the code worked as intended.
answered Nov 21 at 16:57
KC0904
84
add a comment |
up vote
0
down vote
After reviewing the comments by SJR & Gravitate I rechecked my header column. It appears that there was an extra space at the end which once I removed it the code worked as intended.
answered Nov 21 at 16:57
KC0904
84
add a comment |
up vote
0
down vote
up vote
0
down vote
After reviewing the comments by SJR & Gravitate I rechecked my header column. It appears that there was an extra space at the end which once I removed it the code worked as intended.
answered Nov 21 at 16:57
KC0904
84
After reviewing the comments by SJR & Gravitate I rechecked my header column. It appears that there was an extra space at the end which once I removed it the code worked as intended.
answered Nov 21 at 16:57
KC0904
84
answered Nov 21 at 16:57
KC0904
84
answered Nov 21 at 16:57
KC0904
84
answered Nov 21 at 16:57
KC0904
84
84
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416575%2fexcel-vba-auto-sort-once-row-has-been-completed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Works for me. Do you definitely have a column headed "Associate"?
– SJR
Nov 21 at 16:37
1
I'd fully qualify the
Range("Table1[[#All],[Associate]]"). Thisworkbook.sheets("Log")? I'd also suggest you use ThisWorkBook instead of ActiveWorkBook. ActiveWorkbook can cause unexpected errors if you swap to another workbook while the macro is running.– Gravitate
Nov 21 at 16:39
SJR - Yes I have a column header titled Associate.
– KC0904
Nov 21 at 16:41
Thanks Gravitate. After further review my column header had a space at the end of it. Once I removed it the code worked. I also made your recommendations regarding the ThisWorkBook. Thank you for your help.
– KC0904
Nov 21 at 16:47
The above code works like a charm (human error). However once the code has finished it takes me back to the top of the table. Is there a way to keep the focus on the last row completed?
– KC0904
Nov 21 at 18:00