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
<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>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>