ComboBox as filter in me pivot able for dates range

[tamarb55]

hello

My name is Tamar - i want your help with vba code i using in my ComboBox as a filter in my pivot table for date ranges

I recorded a macro but i can't seem to apply that to a simple vba code

Sub Macro1()
'
' Macro1 Macro
'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Dates").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Dates")
        .PivotItems("1/1/2015").Visible = False
        .PivotItems("1/2/2015").Visible = False
        .PivotItems("1/3/2015").Visible = False
        .PivotItems("1/10/2015").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Dates"). _
        EnableMultiplePageItems = True
    Range("I15").Select
End Sub

I also upload my file

i hope you could help me

some of the data is in hebrew

Thanks!!

Tamar

 

[Hui]

Tamar

Firstly, welcome to the Chandoo.org Forums

I rewrote the macro
The dates in the PT are stored in US format of mm/dd/yy except that they display as dd/mm/yy, damn messy

Sub Update_PT()
Dim Pi As PivotItem
Dim DateFrom As String, DateTo As String

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Dates")

  For Each Pi In .PivotItems
  
  myPi = DateSerial(Year(CLng(DateValue(Pi))), Day(CLng(DateValue(Pi))), Month(CLng(DateValue(Pi))))
  
  If CLng(DateValue(myPi)) >= Range("DateFrom").Value2 And CLng(DateValue(myPi)) <= Range("DateTo").Value2 Then
  Pi.Visible = True
  Else
  Pi.Visible = False
  End If

  Next Pi
End With
End Sub

I am not sure if your locale settings will impact this but please let me know how it goes

You will see I have included both the end dates in the formula, you can change the Greater than , less than and Equal signs to suit in the code

 

[tamarb55]

Thank you so much!!!

I have another issue.. this is my vba code for me combobox
i want that after i select item from the list i want to refresh other data connection on me excel file but i get error 1004

"
Private Sub ComboManager_click()

Dim sheet As Worksheet
Dim pt As PivotTable
Dim ptField As Variant
Application.EnableEvents = False
Set sheet = ThisWorkbook.Worksheets("View")
Set pt = ActiveSheet.PivotTables("PivotTable1")
Set ptField = pt.PivotFields("Department Manager")
ptField.CurrentPage = Me.ComboManager.Value

ActiveWorkbook.CONNECTIONS("Query from MS Access Database").Refresh
Application.EnableEvents = True
MsgBox "äðúåðéí òåãëðå", vbInformation + vbMsgBoxRtlReading + vbMsgBoxRight

End Sub

Hope that you could tune me to the answer

Regards,

Tamar

 

[Hui]

Tamar

Please ask this question as a new Thread

It is unlikely that anybody else will read it here

 

[NARAYANK991]

Hi ,

In addition to what Hui has posted , as a general rule , if you want a VBA error resolved , please do the following :

1. Upload a workbook which has data and the code in it

2. If the above is not possible , highlight the line of code which generates the error.

Narayan

 

[tamarb55]

O.K You're right