Using a String Variable in PivotFields option on PivotTable

[smills]

Hi all,
I'm using Excel 2013 and trying to use a string variable in the PivotFields option. I nearly have everything I need working but I keep getting the below error:
PivotFields method of PivotTable class failed


The problem line lies here:

ActiveSheet.PivotTables(pt.Name).PivotFields (datesToPivot)

I have this commented out in entire code sample below. I know it has to have something to do with my string concatenation but I can't spot it.


If I manually type in the date selections as opposed to using a variable it works fine:

ActiveSheet.PivotTables(pt.Name).PivotFields("[Calendar].[Fiscal Year - Fiscal Week - Date].[Date]").VisibleItemsList = Array("[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[2014].&[WK 37].&[2014-10-06T00:00:00]", "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[2014].&[WK 37].&[2014-10-07T00:00:00]", "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[2014].&[WK 37].&[2014-10-08T00:00:00]")

Where concatenation takes place:

datesToPivot = Chr(34) & "[Calendar].[Fiscal Year - Fiscal Week - Date].[Date]" & Chr(34) & ").VisibleItemsList = Array("

datesToPivot = datesToPivot & Chr(34) & "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[" & year & "].&[WK " & week & "].&[" & pDate & "T00:00:00]" & Chr(34) & ", "
lenPivDates = Len(datesToPivot)

datesToPivot = Mid(datesToPivot, 1, lenPivDates - 2)

Entire code sample:

Sub Macro2()


Dim daystouse, lenPivDates As Integer
Dim week, year, pivotStart, datesToPivot, date1, pDate As String
Dim pt As PivotTable


daystouse = 1
datesToPivot = Chr(34) & "[Calendar].[Fiscal Year - Fiscal Week - Date].[Date]" & Chr(34) & ").VisibleItemsList = Array("


'Search for todays date and week
Sheets("Fiscal_Calendar").Select


Columns("D:D").Select
Selection.Find(What:=Format(Date, "YYYY-MM-DD"), After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
year = ActiveCell.Offset(-1, -3)
week = ActiveCell.Offset(-1, -2)
date1 = ActiveCell.Offset(-1, 0)
ActiveCell.Offset(-1, -2).Select


' take away 1 day if before 11am as Retail Cube may not be refreshed
' If Hour(Now) < 11 Then
' daystouse = daystouse - 1
' End If


' Count days to use and start on first day to use
Do
daystouse = daystouse + 1
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell.Offset(-1, 0) <> ActiveCell




'Add each day to use to string to use as criteria in pivot
For i = 1 To daystouse
pivTab = ActiveCell.Value


week = ActiveCell.Value
year = ActiveCell.Offset(0, -1).Value
pDate = ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(1, 0).Select
datesToPivot = datesToPivot & Chr(34) & "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[" & year & "].&[WK " & week & "].&[" & pDate & "T00:00:00]" & Chr(34) & ", "
Next i


' Remove space and comma from string end
lenPivDates = Len(datesToPivot)
datesToPivot = Mid(datesToPivot, 1, lenPivDates - 2)
MsgBox datesToPivot


' Testing add string to cell
Sheets("Fiscal_Calendar").Select
Range("G1") = datesToPivot








For Each ws In ActiveWorkbook.Worksheets
'ws.Select
If Mid(ws.Name, 1, 4) = "Link" Then
ws.Select
For Each pt In ActiveSheet.PivotTables
ActiveSheet.PivotTables(pt.Name).PivotFields( _
"[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year]"). _
VisibleItemsList = Array("")
ActiveSheet.PivotTables(pt.Name).PivotFields( _
"[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Week]"). _
VisibleItemsList = Array("")
ActiveSheet.PivotTables(pt.Name).PivotFields("[Calendar].[Fiscal Year - Fiscal Week - Date].[Date]").VisibleItemsList = Array("[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[2014].&[WK 37].&[2014-10-06T00:00:00]", "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[2014].&[WK 37].&[2014-10-07T00:00:00]", "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[2014].&[WK 37].&[2014-10-08T00:00:00]")
'ActiveSheet.PivotTables(pt.Name).PivotFields (Sheets("Fiscal_Calendar").Range("g1").Value)
'ActiveSheet.PivotTables(pt.Name).PivotFields (datesToPivot)
Next pt
End If
Next ws


Sheets("ROI Dashboard").Select
Range("A1").Select
MsgBox "Refresh complete for previous " & daystouse & " days this fiscal week. Please note that yesterdays sales may not come through until after 11am."




End Sub

Any advice would be greatly appreciated.

 

[RoryA]

You can't put code instructions into a string and expect them to be executed. You can have the pivot field name as a string:

PivotFieldName = "[Calendar].[Fiscal Year - Fiscal Week - Date].[Date]"
ActiveSheet.PivotTables(pt.Name).PivotFields(PivotFieldName).VisibleItemsList = Array("")

and you can create an array of the items you want visible like this:

For i = 1 To daystouse
pivTab = ActiveCell.Value
week = ActiveCell.Value
year = ActiveCell.Offset(0, -1).Value
pDate = ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(1, 0).Select
datesToPivot = datesToPivot & "," & "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[" & year & "].&[WK " & week & "].&[" & pDate & "T00:00:00]"
Next i
DatesArray = Split(datesToPivot, ",")

and then use that like so:

ActiveSheet.PivotTables(pt.Name).PivotFields(PivotFieldName).VisibleItemsList = DatesArray

 

[smills]

Hey, RoryA.

That worked perfectly. Thanks very much.

 

[RoryA]

Glad to help. Welcome to the forum by the way.