Automated Pivot Table

[SteveO59L]

I am using the following as part of an automated pivot table.

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("19-May-03"), "Count of 19-May-03", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False

The 19-May-03 value can change and therefore, I would like to set this value from the value in a specific cell (u7).

The problem being is that when I assign the value in u7 to a variable within the macro, it assumes a format of dd/mm/yy, rather than dd-mmm-yy that the data is in.

Is there a way to convert the date format, within the code ?

TIA

 

[specs]

Hope this helps:

Dim stringDay As String
Dim stringMth As String
Dim stringYr As String
Dim variableString As String
Dim myDate As Date

myDate = 'your variable'

stringDay = Day(myDate)
stringMth = MonthName(Month(myDate))
stringYr = Year(myDate)
variableString = stringDay & "-" & stringMth & "-" & stringYr

 

[SteveO59L]

Ty,

That worked fine for setting the variable, but the

"PivotFields("19-May-03"), "Count of 19-May-03", "

still wont accept the variable.

Thanks anyway

 

[SteveO59L]

* Bump *

Still having problems with this