add prompt for date change in pivot table macro?

[Jaffey]

Hope I picked the right forum....

I have a macro attached to a control button that changes the date on 50 different pivot tables similar to this:

Sheets("AMEX").Select
ActiveSheet.PivotTables("PivotTable55").PivotFields("Date").CurrentPage = _"Mon 11/27"
ActiveSheet.PivotTables("PivotTable38").PivotFields("Date").CurrentPage = _"Mon 11/27"

Currently I do a search & replace using VB editor to change the dates before pressing the button. I would like to prompt the user to input the date instead when the button is pressed. How can I add that? Many thanks.

 

[SkipVought]

Hi,

This question ought to be posted in VBA Visual Basic for Applications (Microsoft) forum707.

Skip,

_[red][/red]

 

[SkipVought]

Sub PickDate()
    Dim pvt As PivotTable
    For Each pvt In Sheets("AMEX").PivotTables
        pvt.PivotFields("Date").CurrentPage = "Mon 11/27"
    Next
End Sub

Skip,

_[red][/red]

 

[Jaffey]

Thanks Skip, after I saw your advice that this should be posted in VB for Applications I went and posted it there without realizing you had also replied with a solution in your next post....so thanks!!

Your solution has also made me realize that there is probably an even better solution for my application; I have about 30 tabs in my spreadsheet with 2-3 pivot tables each, I would like to be able to change the date on all of them without referencing each tab & sheet by name. Can that be done?