amit81_ahl
New Member
- Joined
- May 22, 2018
- Messages
- 1
Hi
I have below dummy data and would like to create pivot table using vba macro. Mutiple users should be allowed to view data as per privilege given.
[FONT="]1) Main data is always available with us[/FONT]
[FONT="]2) Pivot table to prepared through macro. (same has been prepared without macro in the attached sheet)[/FONT]
[FONT="]3) Unit Manager should be able to view details reporting RM's under him. E.g. Bharat should be able to view pivot table for Amit, Rajesh & Sumit. Same way for Karam & Param.[/FONT]
[FONT="]4) Individual RM should be able to view his data only. For example : Amit should be able to view only his data. Similarly for Rajesh, Sumit, Jinu, Prabu.[/FONT]
[FONT="]
[/FONT]
[FONT="]All mapping should be done based on Masterfile (Employee login ids).
[/FONT][TABLE="width: 979"]
<colgroup><col><col><col><col><col span="2"><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]CIS NO[/TD]
[TD]RM NO[/TD]
[TD]RM NAME[/TD]
[TD]Segment[/TD]
[TD]Month End Balance(Non-investment)[/TD]
[TD]Current Balance(Non-investment)[/TD]
[TD]MTD Movement (Non-Investment)[/TD]
[TD]Previous Month End Balance (Investment)[/TD]
[TD]Current Balance (Investment)[/TD]
[TD]MTD Movement (Investment)[/TD]
[TD]Final CM Level Movement - Net Movement[/TD]
[TD]Unit Manager[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 40,000.00[/TD]
[TD] 36,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 50,000.00[/TD]
[TD] 36,000.00[/TD]
[TD] (14,000.00)[/TD]
[TD] (18,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 43,000.00[/TD]
[TD] 39,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 52,000.00[/TD]
[TD] 39,000.00[/TD]
[TD] (13,000.00)[/TD]
[TD] (17,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 46,000.00[/TD]
[TD] 42,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 54,000.00[/TD]
[TD] 42,000.00[/TD]
[TD] (12,000.00)[/TD]
[TD] (16,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 49,000.00[/TD]
[TD] 45,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 56,000.00[/TD]
[TD] 45,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 51,000.00[/TD]
[TD] 47,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 58,000.00[/TD]
[TD] 47,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 53,000.00[/TD]
[TD] 49,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 60,000.00[/TD]
[TD] 49,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 55,000.00[/TD]
[TD] 51,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 62,000.00[/TD]
[TD] 51,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 57,000.00[/TD]
[TD] 53,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 64,000.00[/TD]
[TD] 53,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 59,000.00[/TD]
[TD] 55,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 66,000.00[/TD]
[TD] 55,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 61,000.00[/TD]
[TD] 57,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 68,000.00[/TD]
[TD] 57,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 63,000.00[/TD]
[TD] 59,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 70,000.00[/TD]
[TD] 59,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 65,000.00[/TD]
[TD] 61,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 72,000.00[/TD]
[TD] 61,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 67,000.00[/TD]
[TD] 63,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 71,000.00[/TD]
[TD] 63,000.00[/TD]
[TD] (8,000.00)[/TD]
[TD] (12,000.00)[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 69,000.00[/TD]
[TD] 70,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 70,000.00[/TD]
[TD] 70,000.00[/TD]
[TD] - [/TD]
[TD] 1,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 71,000.00[/TD]
[TD] 72,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 69,000.00[/TD]
[TD] 72,000.00[/TD]
[TD] 3,000.00[/TD]
[TD] 4,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 73,000.00[/TD]
[TD] 74,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 68,000.00[/TD]
[TD] 74,000.00[/TD]
[TD] 6,000.00[/TD]
[TD] 7,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 75,000.00[/TD]
[TD] 76,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 67,000.00[/TD]
[TD] 76,000.00[/TD]
[TD] 9,000.00[/TD]
[TD] 10,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD="align: right"]12349[/TD]
[TD]PRABU[/TD]
[TD]PRIVATE[/TD]
[TD] 77,000.00[/TD]
[TD] 78,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 66,000.00[/TD]
[TD] 78,000.00[/TD]
[TD] 12,000.00[/TD]
[TD] 13,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD="align: right"]12349[/TD]
[TD]PRABU[/TD]
[TD]PRIVATE[/TD]
[TD] 79,000.00[/TD]
[TD] 80,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 65,000.00[/TD]
[TD] 80,000.00[/TD]
[TD] 15,000.00[/TD]
[TD] 16,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD="align: right"]12349[/TD]
[TD]PRABU[/TD]
[TD]PRIVATE[/TD]
[TD] 81,000.00[/TD]
[TD] 82,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 64,000.00[/TD]
[TD] 82,000.00[/TD]
[TD] 18,000.00[/TD]
[TD] 19,000.00[/TD]
[TD]Param[/TD]
[/TR]
</tbody>[/TABLE]
I have below dummy data and would like to create pivot table using vba macro. Mutiple users should be allowed to view data as per privilege given.
[FONT="]1) Main data is always available with us[/FONT]
[FONT="]2) Pivot table to prepared through macro. (same has been prepared without macro in the attached sheet)[/FONT]
[FONT="]3) Unit Manager should be able to view details reporting RM's under him. E.g. Bharat should be able to view pivot table for Amit, Rajesh & Sumit. Same way for Karam & Param.[/FONT]
[FONT="]4) Individual RM should be able to view his data only. For example : Amit should be able to view only his data. Similarly for Rajesh, Sumit, Jinu, Prabu.[/FONT]
[FONT="]
[/FONT]
[FONT="]All mapping should be done based on Masterfile (Employee login ids).
[/FONT][TABLE="width: 979"]
<colgroup><col><col><col><col><col span="2"><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]CIS NO[/TD]
[TD]RM NO[/TD]
[TD]RM NAME[/TD]
[TD]Segment[/TD]
[TD]Month End Balance(Non-investment)[/TD]
[TD]Current Balance(Non-investment)[/TD]
[TD]MTD Movement (Non-Investment)[/TD]
[TD]Previous Month End Balance (Investment)[/TD]
[TD]Current Balance (Investment)[/TD]
[TD]MTD Movement (Investment)[/TD]
[TD]Final CM Level Movement - Net Movement[/TD]
[TD]Unit Manager[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 40,000.00[/TD]
[TD] 36,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 50,000.00[/TD]
[TD] 36,000.00[/TD]
[TD] (14,000.00)[/TD]
[TD] (18,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 43,000.00[/TD]
[TD] 39,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 52,000.00[/TD]
[TD] 39,000.00[/TD]
[TD] (13,000.00)[/TD]
[TD] (17,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 46,000.00[/TD]
[TD] 42,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 54,000.00[/TD]
[TD] 42,000.00[/TD]
[TD] (12,000.00)[/TD]
[TD] (16,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD="align: right"]12345[/TD]
[TD]AMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 49,000.00[/TD]
[TD] 45,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 56,000.00[/TD]
[TD] 45,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 51,000.00[/TD]
[TD] 47,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 58,000.00[/TD]
[TD] 47,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 53,000.00[/TD]
[TD] 49,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 60,000.00[/TD]
[TD] 49,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 55,000.00[/TD]
[TD] 51,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 62,000.00[/TD]
[TD] 51,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD="align: right"]12346[/TD]
[TD]SUMIT[/TD]
[TD]PRIVATE[/TD]
[TD] 57,000.00[/TD]
[TD] 53,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 64,000.00[/TD]
[TD] 53,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 59,000.00[/TD]
[TD] 55,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 66,000.00[/TD]
[TD] 55,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 61,000.00[/TD]
[TD] 57,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 68,000.00[/TD]
[TD] 57,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 63,000.00[/TD]
[TD] 59,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 70,000.00[/TD]
[TD] 59,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD="align: right"]12347[/TD]
[TD]RAJESH[/TD]
[TD]PRIVATE[/TD]
[TD] 65,000.00[/TD]
[TD] 61,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 72,000.00[/TD]
[TD] 61,000.00[/TD]
[TD] (11,000.00)[/TD]
[TD] (15,000.00)[/TD]
[TD]Bharat[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 67,000.00[/TD]
[TD] 63,000.00[/TD]
[TD] (4,000.00)[/TD]
[TD] 71,000.00[/TD]
[TD] 63,000.00[/TD]
[TD] (8,000.00)[/TD]
[TD] (12,000.00)[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 69,000.00[/TD]
[TD] 70,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 70,000.00[/TD]
[TD] 70,000.00[/TD]
[TD] - [/TD]
[TD] 1,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 71,000.00[/TD]
[TD] 72,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 69,000.00[/TD]
[TD] 72,000.00[/TD]
[TD] 3,000.00[/TD]
[TD] 4,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 73,000.00[/TD]
[TD] 74,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 68,000.00[/TD]
[TD] 74,000.00[/TD]
[TD] 6,000.00[/TD]
[TD] 7,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD="align: right"]12348[/TD]
[TD]PRASAD[/TD]
[TD]PRIVATE[/TD]
[TD] 75,000.00[/TD]
[TD] 76,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 67,000.00[/TD]
[TD] 76,000.00[/TD]
[TD] 9,000.00[/TD]
[TD] 10,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD="align: right"]12349[/TD]
[TD]PRABU[/TD]
[TD]PRIVATE[/TD]
[TD] 77,000.00[/TD]
[TD] 78,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 66,000.00[/TD]
[TD] 78,000.00[/TD]
[TD] 12,000.00[/TD]
[TD] 13,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD="align: right"]12349[/TD]
[TD]PRABU[/TD]
[TD]PRIVATE[/TD]
[TD] 79,000.00[/TD]
[TD] 80,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 65,000.00[/TD]
[TD] 80,000.00[/TD]
[TD] 15,000.00[/TD]
[TD] 16,000.00[/TD]
[TD]Param[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD="align: right"]12349[/TD]
[TD]PRABU[/TD]
[TD]PRIVATE[/TD]
[TD] 81,000.00[/TD]
[TD] 82,000.00[/TD]
[TD] 1,000.00[/TD]
[TD] 64,000.00[/TD]
[TD] 82,000.00[/TD]
[TD] 18,000.00[/TD]
[TD] 19,000.00[/TD]
[TD]Param[/TD]
[/TR]
</tbody>[/TABLE]