Filtering specific entry wise columns and then self generating word invoices

Dear All ,

i had a fabulous experience here ,and i am presented with a super challenging case which i assume that experts would love to try out .given the urgency of the case ,i am trying a few other places as well
i would wish the code to be as much commented as possible ,


The case goes as follows ,



Ours is a company XYZ with various units of manufacturing A,B,C,D,E who serve to various parties say A1,A2,A3 etc
Our aim is to calculate total tax liability for each of our units A1,A2,A3,


All this data(OUR VARIOUS UNITS ,various parties ,tax figures ) is in a single parent sheet clustered ,i need to filter it first unitwise partywise breakup and then unitwise partywise consolidated

We have to make partywise service tax and education tax liability statement for each of our units A1,A2,A3



In the excel sheet provided ,there will be parent sheet with embed sheets Party sheet and Unit sheet .....

In The parent sheet itself ,there will be certain columns which the macro will have to fill from emed party sheet and for part 2 of the project where word invoices consolidating unitwise tax liability needs to be generated ,we shall need the Unit embed sheet


In parent sheet ,by purpose certain columns will be marked "user fed "implying that user will fill this data,for example unit name ,Service tax ,party name etc etc .....for the ones which i have marked (to be fed from PARTY Sheet ),the macro will search the embed sheet party and fill in the respective columns Party Code ,Adress ,service tax registration number ,Nature of service from the party sheet (basically party sheet forms database of party sheet )


The column" Unit "in the parent sheet is basically the crux (They represent the various units of our company) ,and when you see the column service provider(say XYZ ),basically that particular unit of XYZ ,I.e A1,A2,A3 etc has taken some service from that service provider (XYZ ),hence has to pay tax for it .....our goal is to create embed sheets unitwise all party tax breakp

Just for knowledge for interested developer taking the project up ,the exercise is to get tax invoices for each unit -In tax column you will see input price say 100 Rs ,then you will see service price (12% =12 Rs )further broken into A-service =8 and B -education cess =3% 4 Rs .The Final goal is to obtain cumulative Unit wise word docs for A and B for each unit ,


How we do it ?


First i want macro to fill the parent sheet parameters after getting data from embed sheet "Party ",then fill in columns for the respective data from party sheet .....if data not available please let macro write Not found but let application not crash please !

Second once done ,let there be a second button which will be the real macro which starts the filter from user fed data in parent sheet as also captured data (into it ).


I want as many embed sheets to be created as many units are there ...say there are 8 units ,hyderbad ,bhopal ,trichy etc ...then let there be 8 embed sheets Hyderabadpartybreakup,trichypartybreakup which shows unitwise party results for the respective columns as attached in the excel sheet ,
Note if a party A has done services 1,2,3,4 for unit A ....so final price is 1+2+3+4
Note if party B has done services 1,2,3,4 for unit A ....so final price is 1+2+3+4
Note if party c has done services 1,2,3,4 for unit A ....so final price is 1+2+3+4
Sheet 2 is both 1,2,3,4 and (1+2+3+4)-breakup for each party for each BHEL unit including total figure for that party
(in sheet all party will be there for that particlar unit and for each party breakp of prices wll be there as well as total meaning 1,2,3,4 as well as 1+2+3+4)
Sheet 3 is simply (Total price 1+2+3+4 )for all parties for each BHEL UNIT

In embed sheets created there will be serial number and original serial number columns ....serial number is 1,2,3,4 but original serial number is the serial number it is from parent sheet ......


Both sheet 2 and sheet 3 is to be done unitwise .

so if there are 8 units ,16 sheets will get created =8 for unitwise partywise breakp of prices ,and 8 for unitwise partywise total (without individual breakup )...this(sheet 3 ) is what will be later needed in word doc .........


In all sheets including parent sheet i want common formatting -font type "calibri"size=10 ,border -only all selected area ,text alignment all to top left ,wrapping done ,

Part -2 -Generating word invoices

Common word doc will get generated for each BHEL unit as per given format in which basically i want macro to fit in a 6 column 2 row data ....

in the word doc ,i want to capture from sheet 3 certain tax columns summation directly -column service tax (total )and column education cess (total )( UNIT PARTY TOTAL embed sheets )and put it in service tax column and education Cess column ............


The word doc is basically unitwise a covering note for tax generation statement ,so for each unit there will be a covering invoice note ,so if there are 6 units ,i want 6 covering notes to be created ,

Inside the word doc ,in the 6 column ,2 columns will be unit location and unit code ......i will provide in the parent sheet a embed sheet for party and another embed sheet for unit ,

In the location and unit code ,the word will read from the excel embed sheet "Unit " and fill in data ,

The green font(You note in word doc ) will remain common .....

the invoice number when you see is

INVOICE NO : ROD/MUM/BHOPAL/MONTH 12-13 ………..


(The green font remains common / unit /month financial year present-later )

Regards ,


Amlan Dutta.

Also posted at Paid section at Ozgrid at the following link ,

(http://www.ozgrid.com/forum/showthre...d=1#post612105)

Haven't had no success to this one ,by experience would prefer someone here to take it up ,this one seems to be the real tough one since one of my best friends could not solve it ,will appreciate help !

Humble Regards ,

Amlan Dutta