How to get VBA to macro the current table rather than the old table it was built on

[Ed_Kelly]

Have a pivot Table (Sales Data). Want to be able to drill down on any number in the pivot table (giving me a new sheet and table number) and be able to run a macro on the drilled down Data to make it visually more appealing. So far so good. When I drill down on another number in the pivot table giving me a new sheet and table# and then run the macro on it the macro looks for the origional table number on which I first wrote the macro.

Is there a line of code that I can use to tell VBA to look at the current table and apply the macro rather than try to always try and find the original table that I first wrote the macro with. Hope that is clear. Have attached excel workbook together with some screen captures


Macro is Cntl + d

Thanks for any and all help

Cannot see how to attache excel workbook?

Ed

Code is a simple macro that formats numbers and then conditionally formats them and totals the table ...just need the macro to work on any new numbered table that may arise because I am drilling down into more data.


Sub DrillDown()
'
' DrillDown Macro
'
' Keyboard Shortcut: Ctrl+d
'
Selection.CurrentRegion.Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table4").Sort.SortFields.Add _
Key:=Range("Table4[[#All],[Date Sold]]"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").ListObjects("Table4").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("H:H").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 5920255
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 5920255
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
Range("G4").Select
ActiveSheet.ListObjects("Table4").ShowTotals = True
Range("Table4[[#Totals],[Customer]]").Select
ActiveSheet.ListObjects("Table4").ListColumns("Customer").TotalsCalculation = _
xlTotalsCalculationCount
Range("Table4[[#Headers],[Date Sold]]").Select
End Sub