Im currently working with pivot tables and facing an issue. Below are the details. Any help on this will be highly appreciated.
Below is the sample table i have created.
<TABLE style="WIDTH: 231pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=307 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Region</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Place</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=105>Product ID</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Revenue</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100001</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100002</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>45900</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100003</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4570</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100004</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>57680</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100005</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>56461</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100004</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>55657</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100005</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>68921</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100001</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>58123</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100002</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>657992</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100003</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>56894</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100004</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>78816</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100005</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>656381</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100006</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>34234</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100007</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>6563</TD></TR></TBODY></TABLE>
Below is the corresponding pivot table.
<TABLE style="WIDTH: 447pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=593 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" span=6 width=49><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=109 height=20>Sum of Revenue</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 85pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=113>Column Labels</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=77></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>Row Labels</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100001</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100002</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100003</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100004</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100005</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100006</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100007</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Grand Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>58123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>657992</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>56894</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>78816</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>656381</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>34234</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>6563</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>1549003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Delhi</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>58123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>657992</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>56894</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>78816</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>656381</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>34234</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>6563</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1549003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>5000</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>45900</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>4570</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>113337</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>125382</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>294189</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Chennai</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>5000</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>45900</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4570</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>57680</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>56461</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>169611</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Hyderabad</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>55657</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>68921</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>124578</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>Grand Total</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>63123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>703892</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>61464</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>192153</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>781763</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>34234</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>6563</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>1843192</TD></TR></TBODY></TABLE>
Below is my VBA Code:
Sub createFile()
Dim pivotElement As PivotFields
Dim pt As PivotTable
Dim counter, RowIndex As Integer
Dim targetFile, strPivotTableName, strRevenue, strRegion, strPlace, strPID As String
Dim pivotElmRegion, pivotElmPlace
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
targetFile = "D:\Jaya\Revenue File.xls"
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Add()
xlApp.DisplayAlerts = False
strPivotTableName = "MyPivot"
xlApp.Cells(1, 1) = "Region"
xlApp.Cells(1, 2) = "Place"
xlApp.Cells(1, 3) = "Revenue"
Set ptPivotTable = Sheets("Sheet2").PivotTables(strPivotTableName)
Set pivotElement = ptPivotTable.PivotFields
RowIndex = 2
strRevenue = "=GETPIVOTDATA(""Revenue"", $A$1"
For Each pivotElmRegion In ptPivotTable.PivotFields("Region").PivotItems
strRegion = strRevenue + ",""Region"",""" + pivotElmRegion.Value + """"
For Each pivotElmPlace In ptPivotTable.PivotFields("Place").PivotItems
strPlace = strRegion + ",""Place"",""" + pivotElmPlace.Value + """)"
MsgBox strPlace
Cells(1, 10) = strPlace
xlApp.Cells(RowIndex, 1) = pivotElmRegion.Value
xlApp.Cells(RowIndex, 2) = pivotElmPlace.Value
xlApp.Cells(RowIndex, 3) = Cells(1, 10)
RowIndex = RowIndex + 1
Next
Next
xlWorkbook.SaveAs (targetFile)
xlApp.Quit
End Sub
The ouput of the macro will be an excel file and the content is given below.
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Region</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Place</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Revenue</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Chennai</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Delhi</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1549003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Chennai</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>169611</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Delhi</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>124578</TD></TR></TBODY></TABLE>
Now let me explain my issue. As i have looped the pivot elements of the Region and Place, it has created a combination of all possible values and tried to fetch them. But certain values does not exist at all. For example, if we look at the source data, there will be no value for North as Region and chennai as Place. Hence i get "REF!" as value.
If we consider North, i need to pick the pivot items of "Place" which exists in North alone.
We can think of deleting the rows with "REF!". But i dont need that solution as my original database contains a lot of rows and columns. This is just a sample.
Hope i explained the problem.
Please help me as soon as possible. Its very urgent.
Thanks in advance,
Jaya
Below is the sample table i have created.
<TABLE style="WIDTH: 231pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=307 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Region</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>Place</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=105>Product ID</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Revenue</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100001</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100002</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>45900</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100003</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4570</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100004</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>57680</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chennai</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100005</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>56461</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100004</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>55657</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100005</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>68921</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100001</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>58123</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100002</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>657992</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100003</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>56894</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100004</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>78816</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100005</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>656381</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100006</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>34234</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delhi</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>100007</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>6563</TD></TR></TBODY></TABLE>
Below is the corresponding pivot table.
<TABLE style="WIDTH: 447pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=593 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" span=6 width=49><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=109 height=20>Sum of Revenue</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 85pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=113>Column Labels</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=49></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=77></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>Row Labels</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100001</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100002</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100003</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100004</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100005</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100006</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>100007</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Grand Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>58123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>657992</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>56894</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>78816</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>656381</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>34234</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>6563</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>1549003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Delhi</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>58123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>657992</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>56894</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>78816</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>656381</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>34234</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>6563</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1549003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>5000</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>45900</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>4570</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>113337</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>125382</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>294189</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Chennai</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>5000</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>45900</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4570</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>57680</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>56461</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>169611</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Hyderabad</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>55657</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>68921</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>124578</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>Grand Total</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>63123</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>703892</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>61464</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>192153</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>781763</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>34234</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>6563</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>1843192</TD></TR></TBODY></TABLE>
Below is my VBA Code:
Sub createFile()
Dim pivotElement As PivotFields
Dim pt As PivotTable
Dim counter, RowIndex As Integer
Dim targetFile, strPivotTableName, strRevenue, strRegion, strPlace, strPID As String
Dim pivotElmRegion, pivotElmPlace
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
targetFile = "D:\Jaya\Revenue File.xls"
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Add()
xlApp.DisplayAlerts = False
strPivotTableName = "MyPivot"
xlApp.Cells(1, 1) = "Region"
xlApp.Cells(1, 2) = "Place"
xlApp.Cells(1, 3) = "Revenue"
Set ptPivotTable = Sheets("Sheet2").PivotTables(strPivotTableName)
Set pivotElement = ptPivotTable.PivotFields
RowIndex = 2
strRevenue = "=GETPIVOTDATA(""Revenue"", $A$1"
For Each pivotElmRegion In ptPivotTable.PivotFields("Region").PivotItems
strRegion = strRevenue + ",""Region"",""" + pivotElmRegion.Value + """"
For Each pivotElmPlace In ptPivotTable.PivotFields("Place").PivotItems
strPlace = strRegion + ",""Place"",""" + pivotElmPlace.Value + """)"
MsgBox strPlace
Cells(1, 10) = strPlace
xlApp.Cells(RowIndex, 1) = pivotElmRegion.Value
xlApp.Cells(RowIndex, 2) = pivotElmPlace.Value
xlApp.Cells(RowIndex, 3) = Cells(1, 10)
RowIndex = RowIndex + 1
Next
Next
xlWorkbook.SaveAs (targetFile)
xlApp.Quit
End Sub
The ouput of the macro will be an excel file and the content is given below.
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Region</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Place</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Revenue</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Chennai</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Delhi</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1549003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>North</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Chennai</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>169611</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Delhi</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>South</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Hyderabad</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>124578</TD></TR></TBODY></TABLE>
Now let me explain my issue. As i have looped the pivot elements of the Region and Place, it has created a combination of all possible values and tried to fetch them. But certain values does not exist at all. For example, if we look at the source data, there will be no value for North as Region and chennai as Place. Hence i get "REF!" as value.
If we consider North, i need to pick the pivot items of "Place" which exists in North alone.
We can think of deleting the rows with "REF!". But i dont need that solution as my original database contains a lot of rows and columns. This is just a sample.
Hope i explained the problem.
Please help me as soon as possible. Its very urgent.
Thanks in advance,
Jaya
