Excel VBA - Portable table sort

Question

This is a follow up to an prior question I put forward which involved a multi process macro that was intended to be portable. I opted to just remove the portion which was giving me fits due to not having portability between sheets or workbooks.

I started building the sort routine, I ended up removing, by recording a macro to do the sort. Then wrote routines to identify current worksheet, and the current table I was focused on. (this was to give portability between sheets.) when I inserted variables for sheet and table names that’s when the whole thing went sideways.

I ended up eliminating it from the sheet and will have the users do their own sort, but I would like to figure it out and thought that maybe someone had done it and had a code fragment that would enlighten me.

I've got two segments of code now that do this with only one issue.

The first half of my code captures the table name in focus into a variable.

I've also got a piece of code that reliably will sort in the manner I want. the only issue that remains is that this second section forces me to enter the text value instead of use variables. This means that the upper portion that identifies the table name of focus is useless, and I would have to create 15 separate subs, one for each worksheet/table combination.

Below is the last of the code. Each of the refrences to "Table126" needs to be replaced with my TableName variable.

I can't figure out how to get this to work!

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Table126")
    Dim sortcolumn As Range
    Set sortcolumn = Range("Table126[LOC]")
    With tbl.Sort
      .SortFields.Clear
      .SortFields.Add(Key:=sortcolumn, Order:=xlDescending, _
       SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(255, 255, 0)
      .Header = xlYes
      .Apply
    End With

@DocBrown Below your prompting for ws. prepending did the trick! Thanks!

I've updated the question to reflect the progress I have made on this. Any help appreciated. Ken... — Ken Carter
– Ken Carter, Commented Nov 22, 2019 at 19:41
Your question is unclear. "can't figure out how to get this to work!" is pretty vague and does not tell us where your problem is. Replacing "Table126" by a variable TableName is pretty straightforward, so what precisely did you try and which error message did you get? Show us the code with the variable TableName, how you initialized it and how you used it, then we might be able to help. — Doc Brown
– Doc Brown, Commented Nov 22, 2019 at 19:57
Maybe this question here Vba loop for many sheets is what you are looking for? — Doc Brown
– Doc Brown, Commented Nov 22, 2019 at 20:03
"Range(" & TableName & "[LOC]")" is clearly syntactical nonsense, and that is not Microsoft's fault. Try something like Set sortcolumn = ws.Range(TableName & "[LOC]"), where TableName is a string variable. If it still does not work, you need to post the exact code "which does not work", and the exact error message, otherwise we cannot help you here. — Doc Brown
– Doc Brown, Commented Nov 22, 2019 at 20:34
Well, if that really turned out to be your problem (which I was not sure about) - as you wish. — Doc Brown
– Doc Brown, Commented Nov 27, 2019 at 7:13

Doc Brown · Accepted Answer · 2019-11-27 07:11:37Z

1

Try something along the lines of

 Set sortcolumn = ws.Range(TableName & "[LOC]")

where TableName is a string variable.

If it still does not work, you need to post a minimal, reproducible example, so we can actually run this thing and see the same error messages you get.

answered Nov 27, 2019 at 7:11

Doc Brown

20.2k7 gold badges56 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA - Portable table sort

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related