dancing-shadow
New Member
- Joined
- Dec 4, 2011
- Messages
- 24
Morning all - I've looked about for a solution to my issue, but I can't work out how to make it work for what I have - I don't have much understanding of arrays and how to handle them...
I have a range of cells in my worksheet containing dates - 13 in total (and will always be 13). These dates are formatted as 'mmm-yy'. I need this list of dates to be added to an array, but in the format 'yyyy-mm-ddT00:00:00', as I need to filter a pivot on this specific array.
I recorded the macro to filter the pivot, and it gave me this, hence why I believe I need the list of dates as a reformatted array:
The code I've been trying to make work I believe is supposed to loop through each item in the array and re-format it. I found the code online somewhere, but I can't find the original source I'm afraid.
I really don't have a clue what I'm doing with this!
I have a range of cells in my worksheet containing dates - 13 in total (and will always be 13). These dates are formatted as 'mmm-yy'. I need this list of dates to be added to an array, but in the format 'yyyy-mm-ddT00:00:00', as I need to filter a pivot on this specific array.
I recorded the macro to filter the pivot, and it gave me this, hence why I believe I need the list of dates as a reformatted array:
Code:
Sub Macro5()
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Range].[Month].[Month]"). _
VisibleItemsList = Array( _
"[Range].[Month].&[2016-12-01T00:00:00]", _
"[Range].[Month].&[2017-01-01T00:00:00]", _
"[Range].[Month].&[2017-02-01T00:00:00]", _
"[Range].[Month].&[2017-03-01T00:00:00]", _
"[Range].[Month].&[2017-04-01T00:00:00]", _
"[Range].[Month].&[2017-05-01T00:00:00]", _
"[Range].[Month].&[2017-06-01T00:00:00]", _
"[Range].[Month].&[2017-07-01T00:00:00]", _
"[Range].[Month].&[2017-08-01T00:00:00]", _
"[Range].[Month].&[2017-09-01T00:00:00]", _
"[Range].[Month].&[2017-10-01T00:00:00]", _
"[Range].[Month].&[2017-11-01T00:00:00]", _
"[Range].[Month].&[2017-12-01T00:00:00]")
End Sub
The code I've been trying to make work I believe is supposed to loop through each item in the array and re-format it. I found the code online somewhere, but I can't find the original source I'm afraid.
Code:
Dim NoArr As Integer, i As Integer
Dim PivAr As Variant
Set OverSht = Temp.Worksheets("Overview")
NoArr = 13
PivAr = OverSht.Range("B23:B35").Value
For i = 1 To NoArr
PivAr(i) = "yyyy-mm-ddT00:00:00" '<<<<Errors on this line
Next
I really don't have a clue what I'm doing with this!