Need some insight: Previous code not working

spydey

Active Member
Joined
Sep 19, 2017
Messages
296
I have some code that works just fine for sorting and filtering a workbook that contains tables.
I have tested it many a time recently and it works as I need it to.
I run the code from within a module in the workbook where the data is located. Let's call this workbook 1.

However, I now need to run the code from within a different workbook, referencing the data workbook. Let's call this workbook 2.

Basically I run the code from within workbook 2 to open workbook 1, sort and filter workbook 1, and do a few other things.

For some reason a single line of code doesn't want to work now and I cannot figure out why.

Here is the basic code from within workbook 2:

Code:
Set Wkb = Workbooks.Open(filename:=nLink, ReadOnly:=False)
Set dws = Wkb.Worksheets("Summary")


With dws.ListObjects("Summary")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Summary[Generate Scorecard]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("Summary[Item]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("Summary[Location]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    .Range.AutoFilter Field:=5, Criteria1:="Yes"
End With
The line of code that is giving me a headache is:

Code:
.Sort.SortFields.Add Key:=Range("Summary[Generate Scorecard]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
I can't figure out why, where this error is.

The only difference between that line in workbook 2 and the same line in workbook 1 is that workbook 1 is:

Code:
.Sort.SortFields.Add Key:=Range("Summary[[#Header],Generate Scorecard]]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
But that also fails in workbook 2.

Any thoughts or ideas as to why this is failing?

The weirdest thing is that when it fails, I go into debug, it highlights that line, I press F8 and then it continues with out any further issues.


I really am at a loss as to why in one workbook it works, but in the other it doesn't, even thought the code is referencing the same workbook and data set.

Thanks for you help and input!!

-Spydey
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,429
Office Version
365, 2010
Platform
Windows, Mobile
Does the below make a difference?

Code:
With dws.ListObjects("Summary")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("Summary[Generate Scorecard]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("Summary[Item]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("Summary[Location]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    .Range.AutoFilter Field:=5, Criteria1:="Yes"
End With
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
233
Not sure I can be much help here, but isn't there an extra bracket?

Code:
.Sort.SortFields.Add Key:=Range("Summary[COLOR=#0000ff][[/COLOR][COLOR=#00ff00][[/COLOR]#Header[COLOR=#00ff00]][/COLOR],Generate Scorecard[COLOR=#0000ff]][/COLOR][COLOR=#ff0000]][/COLOR]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
 

spydey

Active Member
Joined
Sep 19, 2017
Messages
296
Does the below make a difference?

Code:
With dws.ListObjects("Summary")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("Summary[Generate Scorecard]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("Summary[Item]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("Summary[Location]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    .Range.AutoFilter Field:=5, Criteria1:="Yes"
End With

It took me a while to figure out what the difference was between my code and the code you posted. Then I noticed the periods.

I tried it and still get an error message on that line.

It is driving me bonkers trying to figure out what is causing the issue!!!

-Spydey
 

spydey

Active Member
Joined
Sep 19, 2017
Messages
296
Not sure I can be much help here, but isn't there an extra bracket?

Code:
.Sort.SortFields.Add Key:=Range("Summary[COLOR=#0000ff][[/COLOR][COLOR=#00ff00][[/COLOR]#Header[COLOR=#00ff00]][/COLOR],Generate Scorecard[COLOR=#0000ff]][/COLOR][COLOR=#ff0000]][/COLOR]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Yeah, I noticed that too and corrected it but I am still pulling an error on the same line.

-Spydey
 

spydey

Active Member
Joined
Sep 19, 2017
Messages
296
I figured it out.

I had a stray line of code from above what I posted here. It was hiding the active workbook and messing things up. If I comment out that single line, it all works perfectly!!!

I guess that is what I get when I snip code and move things around .... heheheh

Thanks everyone for your help!

-Spydey
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,191
Messages
5,448,878
Members
405,537
Latest member
muh6323

This Week's Hot Topics

Top