dynamic table sorting VBA in Excel 2007

[cometorta]

Hi All,

I am trying to create a dynamic macro to sort a table in excel 2007. I have created the code below. I am hoping to be able to replace the specific table names to the 'active table #' if possible. Can someone point me in the right direction? I appreciate the help. Sincerely. Pancho

Sub Macro1()

ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table4").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table4").sort.SortFields.Add _
Key:=Range("Table4[[#All],[Price$]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table4").sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[capparfrank]

I'm not sure if there's any kind of reference to an 'active' table. But this is one method to consider. I can't vouch for it being the most efficient.

    'loop through each listObject (table) in each worksheet and check if its range intersects with the active cell.
    For Each s In ActiveWorkbook.Worksheets
        For Each li In s.ListObjects
            If Not Intersect(Range(ActiveCell, ActiveCell), li.Range) Is Nothing Then
                'You found the 'active' table.  Do work.
                MsgBox li.Name
            End If
        Next li
    Next s

 

[cometorta]

Hi ALL,

I solved my problem: Please see code below.

Sub getTABLE()
Dim ActiveCellInTable As Boolean
Dim ACell As Range
Set ACell = ActiveCell
ACell.ListObject.Unlist

ActiveSheet.Range("r1:u:u").Select
Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"

ActiveSheet.Range("p").Select
Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"

ActiveSheet.Range("n:n").Select
Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"

' sort Macro
Range("A1").Select
Selection.AutoFilter
Range("A1:v:v").sort Key1:=Range("r1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter

ActiveWindow.SmallScroll ToRight:=10

End Sub

 

[cometorta]

Hi ALL,

I solved my problem: Please see code below.

Sub getTABLE()
Dim ActiveCellInTable As Boolean
Dim ACell As Range
Set ACell = ActiveCell
ACell.ListObject.Unlist

ActiveSheet.Range("r1:u:u").Select
Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"

ActiveSheet.Range("p").Select
Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"

ActiveSheet.Range("n:n").Select
Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"

' sort Macro
Range("A1").Select
Selection.AutoFilter
Range("A1:v:v").sort Key1:=Range("r1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter

ActiveWindow.SmallScroll ToRight:=10

End Sub

Hi All,

I am trying to automatically run the macro, getTABLE(), above every time I double click on the pivot table. I am using excel 2007.

I have tried a few things such as:

Private Sub Workbook_afterdoubleclick(ByVal Sh As Object)
Call getTABLE
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
run getTABLE
End Sub

But they don't seem to work for me. Could someone point in the right direction please?

Thanks in advance,

Pancho