Creating pivot Using VBA in excel second post

[prince bhatia]

hi,

please read the full thread to know what i want to see in result:

this is second post to MR.EXCEL as in first post i didn't get any reply except one from "hike" which i really appreciate

Now , i am creating an excel file file which has two sheets Sheet 1 and sheet 2

in sheet 1 their would be data base which has the columns like :
[TABLE="class: cms_table, width: 1774"]
<tbody>[TR]
[TD="class: cms_table_xl63, width: 103"]Agent Name[/TD]
[TD="class: cms_table_xl63, width: 79"]XID No.[/TD]
[TD="class: cms_table_xl63, width: 118"]Process[/TD]
[TD="class: cms_table_xl66, width: 134"]Transaction date[/TD]
[TD="class: cms_table_xl63, width: 100"]QC MONTH[/TD]
[TD="class: cms_table_xl66, width: 115"]QC date[/TD]
[TD="class: cms_table_xl63, width: 69"]Week[/TD]
[TD="class: cms_table_xl63, width: 102"]QC Done by[/TD]
[TD="class: cms_table_xl69, width: 88"]QC Score[/TD]
[TD="class: cms_table_xl63, width: 149"]Completeness Score[/TD]
[TD="class: cms_table_xl63, width: 163"]City[/TD]
[TD="class: cms_table_xl63, width: 90"]Feedback[/TD]
[TD="class: cms_table_xl63, width: 114"]Improvement Areas[/TD]
[TD="class: cms_table_xl64, width: 164"]Remarks[/TD]
[TD="class: cms_table_xl63, width: 60"]Fail[/TD]
[TD="class: cms_table_xl63, width: 126"]Pass[/TD]
[/TR]
</tbody>[/TABLE]

and in second 2 would be the pivoted of sheet 1 in which their would be two tables first one would be like :

[TABLE="width: 787"]
<colgroup><col span="3"><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Transaction date[/TD]
[TD]Row Labels[/TD]
[TD]Count of XID No.[/TD]
[TD]Count of Pass[/TD]
[TD]Count of Fail[/TD]
[TD]Average of QC Score[/TD]
[TD]Accuracy Score[/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Grand Total[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 799"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 7"][/TD]
[/TR]
[TR]
[TD]Now their would be another table in the sheet 2 having the format like:

[TABLE="width: 612"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Weekly Trend[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Values[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Count of XID No.[/TD]
[TD]Count of Pass[/TD]
[TD]Count of Fail[/TD]
[TD]Average of QC Score[/TD]
[TD]Accuracy Score[/TD]
[/TR]
[TR]
[TD]Week 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]



[/TD]
[TD][/TD]
[TD]






[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Now what i want to create an refresh button in the sheet 1 when i press that refresh button i will get in sheet 2 (pivot sheet ) i automatically get the transaction date row labels( QC date),Count of Xid ,count of pass and count of fail,Average would be of column "j" sheet 1 and accuracy score count of XID pass /count of XID created


now data base for the sheet 1:

[TABLE="width: 1836"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 17"]QUALITY SLA- 95%[/TD]
[/TR]
[TR]
[TD]S.no[/TD]
[TD]Agent Name[/TD]
[TD]XID No.[/TD]
[TD]Process[/TD]
[TD]Transaction date[/TD]
[TD]QC MONTH[/TD]
[TD]QC date[/TD]
[TD]Week[/TD]
[TD]QC Done by[/TD]
[TD]QC Score[/TD]
[TD]Completeness Score[/TD]
[TD]City[/TD]
[TD]Feedback[/TD]
[TD]Improvement Areas[/TD]
[TD]Remarks[/TD]
[TD]Fail[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]171793[/TD]
[TD]New Launch[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]75%[/TD]
[TD] [/TD]
[TD]Mumbai South West[/TD]
[TD] [/TD]
[TD]5E, 3C[/TD]
[TD]Possession Status Incorrect, BN Incorrect- Stop words used. It is an Under Construction project and the completion date will be after 3years- https://housing.com/in/new-projects...everest-builders-developers-in-santacruz-east. "Builders and Developers" is a stop word. We do not use it.[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]171811[/TD]
[TD]Under Construction[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Mumbai Beyond Thane[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]171819[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD]1A[/TD]
[TD]Duplicate Project- r99552[/TD]
[TD]No[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]171935[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]171945[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]171997[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]172049[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Hyderabad[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devesh[/TD]
[TD]172113[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Faridabad[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devrishi[/TD]
[TD]171799[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Chennai South[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devrishi[/TD]
[TD]171805[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Chennai South[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devrishi[/TD]
[TD]171877[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devrishi[/TD]
[TD]171913[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devrishi[/TD]
[TD]171955[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Devrishi[/TD]
[TD]172009[/TD]
[TD]Ready To Move[/TD]
[TD]5/14/2015[/TD]
[TD]May[/TD]
[TD]5/16/2015[/TD]
[TD]Week 2[/TD]
[TD]Anjali[/TD]
[TD]100%[/TD]
[TD] [/TD]
[TD]Pune[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

i also posted the same thread in orzid but didn't find any complete resolution please find the link this would be help you to understand what i want to create and how it would help you also accuracy in vba but date wise.. please rely only if you can understand and can find a correct resolution