Using a String Variable in PivotFields option on PivotTable

smills

New Member
Joined
Oct 9, 2014
Messages
2
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:
Code:
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:
Code:
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:
Code:
datesToPivot = Chr(34) & "[Calendar].[Fiscal Year - Fiscal Week - Date].[Date]" & Chr(34) & ").VisibleItemsList = Array("
Code:
datesToPivot = datesToPivot & Chr(34) & "[Calendar].[Fiscal Year - Fiscal Week - Date].[Fiscal Year].&[" & year & "].&[WK " & week & "].&[" & pDate & "T00:00:00]" & Chr(34) & ", "
lenPivDates = Len(datesToPivot)
Code:
datesToPivot = Mid(datesToPivot, 1, lenPivDates - 2)




Entire code sample:
Code:
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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:
Code:
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:
Code:
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:
Code:
ActiveSheet.PivotTables(pt.Name).PivotFields(PivotFieldName).VisibleItemsList = DatesArray
 
Upvote 0
Glad to help. :biggrin: Welcome to the forum by the way.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top