Need some insight: Previous code not working

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top