Updating Pivot Table Date Filter

[hartwell]

I am trying to change the filter of a date drop down when refreshing a pivot table via VBA my current code is as follows...

Dim myweekstart As Date
myweekstart = Worksheets("data").Range("D1").End(xlDown).Value
Range("b3").value = myweekstart

B3 is the cell ref of the drop down box in the pivot table

 

[hartwell]

should mention that it says the value doesnt exist (which it does) and askes if it should rename it to the new value

 

[xlbo]

You need to reference the pivottable object to change the selection. AFAIK, you cannot just chnage the cell itself. If you can just change the value, I would imagine that it would need EXACTLY the same formatting as the data it is replacing so try:

Dim myweekstart As Date
myweekstart = format(Worksheets("data").Range("D1").End(xlDown).Value,Worksheets("data").Range("D1").End(xlDown).numberformat)
Range("b3").value = myweekstart

 

[SkipVought]

Hi,

Is the drop down a Pivot Table Page Field? Check out the CurrentPage property.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}