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

[Ed_Kelly]

https://www.screencast.com/t/N93HjymQ

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

Ed

PS cannot see a way to attach the excel file...please advise


Here is the macro

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

<colgroup><col></colgroup><tbody>
</tbody>

<fieldset class="postcontent" style="margin: 5px 0px; padding: 0px; border-width: 0px; border-style: initial; border-color: initial; word-wrap: break-word;"></fieldset>