Results 1 to 6 of 6

Thread: Need some insight: Previous code not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Location
    Houston, TX
    Posts
    275
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need some insight: Previous code not working

    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

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,872
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need some insight: Previous code not working

    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
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    178
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need some insight: Previous code not working

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

    Code:
    .Sort.SortFields.Add Key:=Range("Summary[[#Header],Generate Scorecard]]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

  4. #4
    Board Regular
    Join Date
    Sep 2017
    Location
    Houston, TX
    Posts
    275
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need some insight: Previous code not working

    Quote Originally Posted by MARK858 View Post
    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

  5. #5
    Board Regular
    Join Date
    Sep 2017
    Location
    Houston, TX
    Posts
    275
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need some insight: Previous code not working

    Quote Originally Posted by Twollaston View Post
    Not sure I can be much help here, but isn't there an extra bracket?

    Code:
    .Sort.SortFields.Add Key:=Range("Summary[[#Header],Generate Scorecard]]"), 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

  6. #6
    Board Regular
    Join Date
    Sep 2017
    Location
    Houston, TX
    Posts
    275
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need some insight: Previous code not working

    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 by spydey; Jun 25th, 2019 at 09:51 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •