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

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

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