Add dates from range to array and reformat

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:


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!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,122,819
Messages
5,598,293
Members
414,223
Latest member
Accountant2B

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
Top