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

[suyogpat]

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.

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

<tbody>
</tbody>

 

[James006]

Hello,

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

[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

 

[suyogpat]

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.

 

[James006]

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

 

[AkudeNikhil]

I have created a one pivot table. but not select in yesterday date. please kindly support.