Macro to select date field from Pivot Table --- PLease HELP

[ccofmars]

Hi I am using Excel 2010. My level of VBA is below novice and I need some help creating a macro that will prompt a message box asking to type of date and that consequently only select the date in the pivot table.

It is an attendance report that I am building.
I have already created a button that prompts a macro to refresh the pivot.
I need a macro that will prompt the user to type of date (message box) and that will select the date typed in the pivot to show the employees that are present or absent on that date.

please help!ray:ray:

I thank you in advance.

 

[Derek Brown]

Have a look at PT0022 in the 'PivotTables' link on the following page:
http://www.contextures.com/excelfiles.html
Although it is for 2007, it may work for 2010.

 

[ccofmars]

Hi Derek

It is an excellent start. that was exactly what I was looking for. However, I have having an issue due to the fact that I do not have a column "Date".

I have a index match and to make a long story short, my columns are as follows:


-- removed inline image ---
Sites | Modality | Employees | 1-Sep | 2-sep | 3-Sep | etc....

I tried to change things around in the macro to make it work, but I was unsuccessful

I do not know how to check and uncheck fields in the pivot via macro.

your help is greatly appreciated... I am also there thanks to your help though.

 

[Derek Brown]

You mentioned 'date' in your post (I guess that you meant 'data') which is why I pointed you at that particular page.
Have a look at PT0013 (Filter From Worksheet Selection) on that same page - that does the filtering for one field (in the example) by setting up a true or false column that can be filtered in the pivot table, instead of using VBA.
You are using Excel 2010 - have you looked at 'Slicers'?
You will be able to watch an example at:
http://www.youtube.com/watch?v=KG593BIWV7U

 

[ccofmars]

WOW... thanks for the info as I did not know what a slicer was. It was definitely informative for my next project. Regarding the report I am building, it does not work as I am getting "slices" of each date. I did mean Date (not data). I wish I could attach a picture at what I am looking at since the description of my pivot table is not very clear.

What I am trying to accomplish is have a macro prompting a message box that request the user to type a date, the date would then be "checked" in the fields list of the pivot table.

Again thank you so much for providing feedback.