+ Reply to Thread
Results 1 to 4 of 4

Sort table on inactive sheet

Hybrid View

Sthlm Sort table on inactive sheet 11-28-2019, 07:44 AM
romperstomper Re: Sort table on inactive... 11-28-2019, 07:59 AM
Sthlm Re: Sort table on inactive... 11-29-2019, 03:32 AM
romperstomper Re: Sort table on inactive... 11-29-2019, 04:56 AM
  1. #1
    Registered User
    Join Date
    06-21-2012
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    48

    Sort table on inactive sheet

    I've been working with a script for years, but due to some changes it now also needs to work when the sheet is inactive.

    Sub Sort_Skill_Table()
    '
    ' Sort TBL_SKILLS on FC and DEPARTMENT columns
    
    
        ActiveWorkbook.Worksheets("LISTS").ListObjects("TBL_SKILLS").Sort.SortFields. _
            Clear
        ActiveWorkbook.Worksheets("LISTS").ListObjects("TBL_SKILLS").Sort.SortFields. _
            Add Key:=Range("TBL_SKILLS[FC]"), SortOn:=xlSortOnValues, Order:= _
            xlDescending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("LISTS").ListObjects("TBL_SKILLS").Sort.SortFields. _
            Add Key:=Range("TBL_SKILLS[DEPARTMENT]"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("LISTS").ListObjects("TBL_SKILLS").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Any ideas?

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,013

    Re: Sort table on inactive sheet

    Try this:

    Sub Sort_Skill_Table()
    '
    ' Sort TBL_SKILLS on FC and DEPARTMENT columns
    
       Dim ListTable As ListObject
       Set ListTable = ActiveWorkbook.Worksheets("LISTS").ListObjects("TBL_SKILLS")
       
       With ListTable.Sort
          With .SortFields
             .Clear
             .Add Key:=ListTable.ListColumns("FC").Range, SortOn:=xlSortOnValues, _
                  Order:=xlDescending, DataOption:=xlSortNormal
             .Add Key:=ListTable.ListColumns("DEPARTMENT").Range, SortOn:=xlSortOnValues, _
                  Order:=xlAscending, DataOption:=xlSortNormal
          End With
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
       End With
    End Sub
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    06-21-2012
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    48

    Re: Sort table on inactive sheet

    It doesn't have the desired effect. In the sort column there are 'yes' and 'no' values which changed based on info from a different sheet. When changed the input the value does change as expected in the table. But it doesn't sort the table. Only when I manually change a value in the table directly it does the sort as expected.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,013

    Re: Sort table on inactive sheet

    I don't follow. That code has to be triggered by you, so it's not going to care how/whether you change anything.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cut data from inactive sheet and paste them in active sheet after last row
    By Roshan.Shakya in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-08-2019, 09:50 AM
  2. set range for inactive sheet
    By Nitzan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2014, 08:42 AM
  3. [SOLVED] VBA - Sort selection on inactive sheet
    By ormerods in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-20-2013, 11:30 AM
  4. Clear Filters From Inactive Sheet VBA
    By jaslake in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-09-2013, 07:24 AM
  5. sort if sheet inactive
    By MeenuSaini in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2010, 05:13 AM
  6. Sheet Inactive Macro
    By Zimbo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-12-2008, 04:05 AM
  7. Calling an inactive sheet...
    By RJH in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2006, 11:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1