Need some insight: Previous code not working

[spydey]

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:

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:

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

.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

 

[MARK858]

Does the below make a difference?

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]

Not sure I can be much help here, but isn't there an extra bracket?

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

Does the below make a difference?

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]

Not sure I can be much help here, but isn't there an extra bracket?

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

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