Segregation of Excel Worksheet

[arviy2k]

Hi,

I'm using Office 2010. I have an excel workbook which is the summary of expenses for all cost centers for a year, and a few pivot tables that link to this data.

What I need, is individual reports for each cost center. How I do it currently is, if there are 5 cost centers, I create 5 copies of the file, delete data for all cost centers except one and refresh the pivots. Is there any way to automate this using VBA? Automation will help save a lot of time and effort.

I have uploaded a sample file. This file is a consolidation of all the cost centers assigned to me. I need individual files created for each cost center. The actual file contains more data columns and pivot sheets, but this file is easier to understand. In the sample file, cost center is Column A on the data worksheet.

http://www.mediafire.com/?9h1z4sbyohr6bot

What needs to be done:
1. Data for all except one cost center to be deleted and pivots refreshed.
2. New file to be created with all sheets of the workbook, saved with the cost center name suffixed to the file.
3. Process to be repeated for each additional cost center in the data sheet.

Thank you so much for your help. Really appreciate it.

Regards,
Arvind.

 

[flakeydove]

Hi,

Rather than deleting the other cost centers can't you just have cost center as the pivot filter. eg

<TABLE style="WIDTH: 232pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=309><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #002776; WIDTH: 116pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl1075 height=20 width=155>Cost Center</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl1076 width=62>880</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl1066 width=92></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl1067 height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl1067></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl1067></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl1069 height=17>Sum of Expense Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl1069>Period</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl1070> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl1071 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002776; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1074>PD 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #002776; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1074>Grand Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1073 height=20>Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #92d050; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1072 align=right>304.13 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #92d050; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1072 align=right>304.13 </TD></TR></TBODY></TABLE>

Apologies if I haven't understood your question.

Joe

 

[arviy2k]

Hi Joe,

While a pivot filter will help me get information about a specific cost center, it does not fulfill the purpose of segregation. Individual files need to be created for each cost center. So if there are 5 unique cost centers, I need 5 separate files to be sent to the business leader.

Hope this clarifies it a bit. Thank you for the response.

Regards,
Arvind