Need VBA code to for pivot to select particular date and data related to the date

S

suyogpat

New Member
Joined
Jun 29, 2017
Messages
28
Hi All,

I have a daily task where from pivot i have to select yesterday's date and copy the data into another sheet.. is there any way by which i can use macro for the same. What i Do daily is
1- from pivot i select yesterdays date from "create date" column and then copy the data in another sheet.

i am trying to achieve here is in output cell of "A1" i will have ysterday's date , macro should pick up that date , and run the same in pivot of "create date", (which is column J), Then 2nd condition is he should filter "pitched,Mandate" from "stage" column which is column C, if he found any data then, it should be copied to another sheet. if no data found macro should copy only pivot headers.

below is the test data.. I am sorry that i can not attach file here..so just copying the pivot Data here.. thanks all in advance for your help. appreciate your input for the same.

[TABLE="width: 1099"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mandate Date[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lost Date[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Sum of Sub ID Rev[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sub ID[/TD]
[TD]Final Family Name[/TD]
[TD]Stage[/TD]
[TD]Country[/TD]
[TD]Member[/TD]
[TD]AM[/TD]
[TD]Team[/TD]
[TD]E/G[/TD]
[TD]Prod Level 2[/TD]
[TD]Create Date[/TD]
[TD]Close Date[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]11223353[/TD]
[TD]sodexo-1[/TD]
[TD]MANDATE[/TD]
[TD]nepal[/TD]
[TD]test10[/TD]
[TD](blank)[/TD]
[TD]mid corp[/TD]
[TD]E[/TD]
[TD]test10[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]98687[/TD]
[/TR]
[TR]
[TD]11223352[/TD]
[TD]dell-1[/TD]
[TD]COMPLETED[/TD]
[TD]ladakh[/TD]
[TD]test9[/TD]
[TD](blank)[/TD]
[TD]small corp[/TD]
[TD]G[/TD]
[TD]test9[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]87545[/TD]
[/TR]
[TR]
[TD]11223346[/TD]
[TD]facebook-1[/TD]
[TD]DEAD[/TD]
[TD]goa[/TD]
[TD]test3[/TD]
[TD](blank)[/TD]
[TD]Large Corporate[/TD]
[TD]G[/TD]
[TD]test3[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]69874[/TD]
[/TR]
[TR]
[TD]11223345[/TD]
[TD]Asian Paints-1[/TD]
[TD]MANDATE[/TD]
[TD]Mumbai[/TD]
[TD]test2[/TD]
[TD](blank)[/TD]
[TD]Large Corporate[/TD]
[TD]G[/TD]
[TD]test2[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]15/04/2019[/TD]
[TD="align: right"]58687[/TD]
[/TR]
[TR]
[TD]11223350[/TD]
[TD]calender-1[/TD]
[TD]DONE AWAY[/TD]
[TD]hyderabad[/TD]
[TD]test7[/TD]
[TD](blank)[/TD]
[TD]small corp[/TD]
[TD]G[/TD]
[TD]test7[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]56987[/TD]
[/TR]
[TR]
[TD]11223349[/TD]
[TD]end-1[/TD]
[TD]COMPLETED[/TD]
[TD]chennai[/TD]
[TD]test6[/TD]
[TD](blank)[/TD]
[TD]mid corp[/TD]
[TD]E[/TD]
[TD]test6[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]15/04/2019[/TD]
[TD="align: right"]52689[/TD]
[/TR]
[TR]
[TD]11223348[/TD]
[TD]stat-1[/TD]
[TD]PROSPECTING[/TD]
[TD]Bangalore[/TD]
[TD]test5[/TD]
[TD](blank)[/TD]
[TD]small corp[/TD]
[TD]G[/TD]
[TD]test5[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]03/04/2019[/TD]
[TD="align: right"]42698[/TD]
[/TR]
[TR]
[TD]11223347[/TD]
[TD]gmail-1[/TD]
[TD]DONE AWAY[/TD]
[TD]Delhi[/TD]
[TD]test4[/TD]
[TD](blank)[/TD]
[TD]Large Corporate[/TD]
[TD]E[/TD]
[TD]test4[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]42159[/TD]
[/TR]
[TR]
[TD]11223344[/TD]
[TD]Nerolac-1[/TD]
[TD]PITCHED[/TD]
[TD]India[/TD]
[TD]test1[/TD]
[TD](blank)[/TD]
[TD]Large Corporate[/TD]
[TD]G[/TD]
[TD]test1[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]03/04/2019[/TD]
[TD="align: right"]15487[/TD]
[/TR]
[TR]
[TD]11223351[/TD]
[TD]avaya-1[/TD]
[TD]PROSPECTING[/TD]
[TD]kolkata[/TD]
[TD]test8[/TD]
[TD](blank)[/TD]
[TD]mid corp[/TD]
[TD]E[/TD]
[TD]test8[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]02/04/2019[/TD]
[TD="align: right"]2444[/TD]
[/TR]
[TR]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]527257[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Hello,

Most probably your Field : Create Date is used as a Page filter ...

Code: 
[COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Sheet1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotTables[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"PivotTable1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]        [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotFields[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Create Date"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]CurrentPage [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Date-1[/FONT][/COLOR]

Hope this will help
 
Upvote 0
Hi James,

Thanks for the quick reply, but here i am looking to have two pivot fields.. as 1st criteria is "Create Date" and 2nd pivot filter should be on "stage",I need to select 2 conditions from stage column along with the create date..
and also if there is no data available for the previous date i need macro to copy only headers else should copy the data..

Thanks again in advance.
 
Upvote 0
Hi,

Without the file rather difficult to progress ...

An easy solution is for you to start your macro recorder ...

and go through your process once ...

Then, on that basis ... you will improve your macro ...

Hope this will help
 
Upvote 0
I have created a one pivot table. but not select in yesterday date. please kindly support.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
295
Skyybot
Skyybot
B
  • Question Question
Delete content of cells / move up remaining data in multiple worksheets based on a range of blank cells in each worksheet
Replies
0
Views
466
Blanchetdb
B
C
  • Question Question
Match and retrieve values from another workbook
Replies
3
Views
522
Cortex1000
C
C
  • Solved
Copy the missing cells from another workbook
Replies
4
Views
491
Max1616
M
M
  • Solved
Calculate headcount at the end of each month in 2019 based on hire and termination date
Replies
5
Views
5K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

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
Back
Top