Create Pivot Table from Userform Search

[paulstan]

I want to create a dynamic Pivot Table based on a search criteria from a Userform. The criteria will be a date search (from and to dates – 2 separate text boxes). When submitting the Userform, all records in the user-selected range will then be displayed in the Pivot Table, for example show all records between dates 01/6/11 and 30/6/11.


All the source will be coming from the same Excel Worksheet (no external data).


Will this require mounds and mounds of VBA coding, or is there a simpler solution.


Note: I’m at the Novice stage of VBA/SQL?


(In anticipation) Many thanks

 

[Hui]

Paulstan


I would already have a pivot table setup and then use the user form to change the date field within the existing pivot table.


Without knowing how your user form is setup I assume you mean a VBA User Form?


If So you probably have added an OK or Accept button to activate the activity when the dates are seledcted.


If so Right Click the Button and view code


You will need to add some code to the CommandButton Click event like:

[pre]

Private Sub CommandButton1_Click()

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
Type:=xlDateBetween, Value1:=TextBox1.Value, Value2:=TextBox2.Value

'ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
Type:=xlDateBetween, Value1:="1/08/2011", Value2:="31/10/2011"

' Change Worksheet, Table and Pivot Field Nmaes to suit

End Sub

[/pre]

The tricky part is that Excel probably expects the dates to be entered as text strings


You may have to play with that a bit.


You can email me if required, Click Excel Ninja to the left, email is at bottom of page