Refresh Pivot and select name from drop down box

[hanz753]

Hello, I have a repetitive task in excel, I need to refresh a pivot table select the name and copy and paste data. This is what I have done so far.

Sheets("Pivot").Select
Range("B15").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").CurrentPage = _
"Alan O'Neill"
Sheets("Targets").Select
Range("B5").Select
Selection.Copy
Application.Goto Reference:="Alan_ONeill"

Sheets("Pivot").Select
Range("B22").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").CurrentPage = _
"Andrew Ferry"
Sheets("Targets").Select
Selection.Copy
Application.Goto Reference:="Andrew_Ferry"

I need to know how to refresh and select the name in the drop down box.

Kind Regards

 

[mrmmickle1]

This is how to refresh a pivot table:

Sub PivotRefresh()


    Dim pt As PivotTable


    Set pt = ActiveSheet.PivotTables("YourPivot")
    pt.RefreshTable


End Sub

Depending on what kind of Drop Down you have the code could be different. Is the Dropdown?:

1. Data Validation List
2. Form Control ComboBox
3. ActiveX ComboBox

 

[hanz753]

I am not sure how to tell which drop box it is.

To refresh the pivot, How would I include your code into my mine?

Much Appreciated

 

[mrmmickle1]

Is the drop box in a cell? Or is it on top of the worksheet? When you right click it what name does it have in the upper left hand corner?

 

[hanz753]

Hello,

The drop box is not at the top of the worksheet and name in the upper name in the upper left hand corner says "Drop Down 14".

The drop box is big and cover 12 cells.

Thank you