How prompt for date input in Pivot Table? 1

[Jaffey]

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.

 

[Jaffey]

Pls ignore this post - Skip replied to it with a solution in the Visual Basic(Microsoft): Version 5 & 6 forum.

 

[Andrzejek]

I don't know much about pivot tables, but
is that the part you edit in VB editor?

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

If so, Date is a reserved word and it gives you current date.

Try:

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

But if you need to replace "Mon 11/27" try

MsgBox Format$(Weekday(Date, vbSunday), "ddd") & " " & Day(Date) & "/" & Month(Date)

It should give you Mon 11/12

HTH

---- Andy

 

[SkipVought]

Hi,

dim ws as worksheet, pvt as pivottable, dMyDate as Date
dMyDate = inputbox("enter date")
for each ws in worksheets
  for each pvt in ws.pivottables
    pvt.").PivotFields("Date").CurrentPage = Format(dMyDate, "ddd mm/dd")
  next
next

Skip,

_[red][/red]

 

[Jaffey]

Skip, your solution works fine on my machine but when the intended user tries it from hers she gets run time error: "Unable to set the Default Property of the PivotItem Class". I thought it might have something to do with her macro security settings but they are already set to low. Then I noticed she's only using MS Excel 2000.

 

[SkipVought]

On her machine, is the field named "Date" and is it the PAGE field?

Skip,

_[red][/red]

 

[Jaffey]

It's the exact same spreadsheet....we both access it from the same shared folder.

 

[SkipVought]

oops. I saw something I did not like...

dim ws as worksheet, pvt as pivottable, dMyDate as Date
dMyDate = inputbox("enter date")
for each ws in worksheets
  for each pvt in ws.pivottables
    pvt.PivotFields("Date").CurrentPage = Format(dMyDate, "ddd mm/dd")
  next
next

PLEASE replace your code.

Skip,

_[red][/red]

 

[Jaffey]

Skip, I had already removed the .") typo. After she got the error I opened it again on my machine and it still runs fine.

 

[SkipVought]

try this...

pvt.PivotFields("Date").CurrentPage[b].name[/b] = Format(dMyDate, "ddd mm/dd")

Skip,

_[red][/red]

 

[Jaffey]

your change works for me but now she's getting a name not defined error on her machine

 

[SkipVought]

Try Help/Detect & repair.

Short of that, reload Office

Skip,

_[red][/red]

 

[Jaffey]

The exact error is "Unable to set the Name Property of the PivotItem Class". Does that help?

 

[SkipVought]

Try Help/Detect & repair.

Short of that, reload Office

Skip,

_[red][/red]