I have the below code that goes through pivot tables/charts and makes certain dates invisible:
Sub ChangeStartingDatePoint_Macro_modifiedToIncludeSpecificDates()
Application.ScreenUpdating = False '' this makes last 7 days visible
'' assuming all days are visible beforehand
Dim ws As Worksheet
Dim objPT As PivotTable
Dim objPTField As PivotField
Dim objPTItem As PivotItem
Dim LastDate As Date
Dim ALastDate As Date
Dim BLastDate As Date
Dim FirstDate As Date
FirstDate = Sheets("Sheet1").Range("A2").Value
LastDate = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)
MsgBox "Last Date is:" & LastDate & vbCrLf & "First Date is:" & FirstDate & vbCrLf
ALastDate = Sheets("Sheet2").Range("G5").Value
BLastDate = Sheets("Sheet2").Range("H5").Value
MsgBox "ALastDate is:" & ALastDate & vbCrLf & "BLastDate is:" & BLastDate & vbCrLf
For Each ws In Worksheets
ws.Visible = xlSheetVisible
For Each objPT In ws.PivotTables
' MsgBox "Pivot table name: " & objPT.Name & vbCrLf & "Sheet name: " & ws.Name & vbCrLf & "test"
With objPT
'
' Run through all the row fields
For Each objPTField In .RowFields
MsgBox "objPTField is:" & objPTField.Value & vbCrLf
For Each objPTItem In objPTField.VisibleItems
If objPTItem.Value < ALastDate Then
'' MsgBox "FirstDate is:" & FirstDate & vbCrLf & "Lastdate7 is:" & LastDate7 & vbCrLf
MsgBox "objPTItem is:" & objPTItem.Value & vbCrLf
objPTItem.Visible = False
'FirstDate = FirstDate + 1
End If
If objPTItem.Value > BLastDate Then
'' MsgBox "FirstDate is:" & FirstDate & vbCrLf & "Lastdate7 is:" & LastDate7 & vbCrLf
MsgBox "objPTItem is:" & objPTItem.Value
objPTItem.Visible = False
'LastDate = LastDate - 1
End If
Next 'objPTItem
Next 'objPTField
End With
FirstDate = Sheets("Sheet1").Range("A2").Value
Next objPT
Next ws
Application.ScreenUpdating = True
End Sub
But this line of code shows me that the date is in the format mm/dd/yyyy. How do I rectify this?
MsgBox "objPTItem is:" & objPTItem.Value & vbCrLf
As this messes up my if statement
If objPTItem.Value < ALastDate Then
'' MsgBox "FirstDate is:" & FirstDate & vbCrLf & "Lastdate7 is:" & LastDate7 & vbCrLf
MsgBox "objPTItem is:" & objPTItem.Value & vbCrLf
objPTItem.Visible = False
'FirstDate = FirstDate + 1
End If
It is stored in the format dd/mm/yyyyy, but not sure how to correct the conversion??
Any ideas?
Thanks
Bookmarks