|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
need VBA to Transpose the Data from excel to word based on given criteria(status)
Hi,
I need an VBA to Transpose the Data from excel to word based on given criteria(status) I have an excel sheet Properties I want to transpose the few records from sheet Properties from to msword as per given below format by clicking Macro button (EXPORT PROPERTIES) *RESIDENTIAL PROPERTIES ON *RENT (*RESIDENTIAL/COMMERCIAL/AGRICULTURAL) (*RENT/SALE) Property Code: Value from Column C Property Type: Value from Column F Flat type: Value from Column G Area: Value from Column H Location: Value from Column L Furnishing: Value from Column M *Size: Value from Super Built-up: (Value from Column O) & (Column N) / Built-up: (Value from Column P) & Unit : ( Column N) / Carpet: (Value from Column Q) & Unit : ( Column N) Floor Number: Value from Column R Total Floor: Value from Column S Age of the property: Value from Column T Rent: Value from Column U / (Column X) Deposit: Value from Column V Sale Price: Value from Column W / (Column X) Property Description: Value from Column AA Proposal: Value from Column AB 1) The above details should come one by one in msword in one page after clicking the Macro button. 2) If any of the value of the above in sheet Properties is blank or - then particular should be disappeared / skipped automatically for particular property code. Refer example A below. 3) Is *Size field: if any of the value is blank or - then particular field&value should be disappeared 4) All the selected properties (To be transposed) should be transposed in alphabetical order based on Location Column H and then ascending order based on serial number in Column A 5) When I am Clicking on Macro button there should be a a) pop up window to enter the Transaction of the status based on Column AZ. (Like pop up window stating Please enter Status. The data input value should be be any from column AZ) b) pop up window to enter the Transaction of the properties based on Column D. (Like pop up window stating Please enter Transaction. The data input value should be 1) Rent, 2) Sale & 3) Rent/Sale as in column D) c) pop up window to enter the Structure of the properties based on Column E. (Like pop up window stating Please enter Structure. The data input value should be 1) Residential & 2) Commercial and 3) Both Residential & Commercial as in column E) d) pop up window to enter the Flat type of the properties based on Column G. (Like pop up window stating Please enter Flat type. The data input value should be any from column G or any drop down menu) e) pop up window to enter the Location of the properties based on Column H. (Like pop up window stating Please enter Location The data input value should be any from column H or any drop down menu) 6) Incase of Multiple selection, the transposed order on msword should be as in Example B Note: I will keep adding an rows to insert new records(Properties). Also I might add column in between any column to capture more details. Example A If Value in Column M , U, V and AB is missing then exported record should as below Property Code: Value from Column C Property Type: Value from Column F Flat type: Value from Column G Area: Value from Column H Location: Value from Column L *Size: Value from Super Built-up: (Value from Column O) & (Column N) / Built-up: (Value from Column P) & Unit : ( Column N) / Carpet: (Value from Column Q) & Unit : ( Column N) Floor Number: Value from Column R Total Floor: Value from Column S Age of the property: Value from Column T Sale Price: Value from Column W / (Column X) Property Description: Value from Column AA Example B *RESIDENTIAL PROPERTIES ON *RENT 1) Property Code: Value from Column C Property Type: Value from Column F Flat type: Value from Column G Area: Value from Column H Location: Value from Column L Furnishing: Value from Column M *Size: Value from Super Built-up: (Value from Column O) & (Column N) / Built-up: (Value from Column P) & Unit : ( Column N) / Carpet: (Value from Column Q) & Unit : ( Column N) Floor Number: Value from Column R Total Floor: Value from Column S Age of the property: Value from Column T Rent: Value from Column U / (Column X) Deposit: Value from Column V Sale Price: Value from Column W / (Column X) (**This Field should not be there if it is a Rent properties) Property Description: Value from Column AA Proposal: Value from Column AB 2) Property Code: Value from Column C Property Type: Value from Column F Flat type: Value from Column G Area: Value from Column H Location: Value from Column L Furnishing: Value from Column M *Size: Value from Super Built-up: (Value from Column O) & (Column N) / Built-up: (Value from Column P) & Unit : ( Column N) / Carpet: (Value from Column Q) & Unit : ( Column N) Floor Number: Value from Column R Total Floor: Value from Column S Age of the property: Value from Column T Rent: Value from Column U / (Column X) (**This Field should not be there if it is a Sale properties) Deposit: Value from Column V (**This Field should not be there if it is a Sale properties) Price: Value from Column W / (Column X) Property Description: Value from Column AA Proposal: Value from Column AB 3) 4) 5) Continues *RESIDENTIAL PROPERTIES ON *SALE 6) Property Code: Value from Column C Property Type: Value from Column F Flat type: Value from Column G Area: Value from Column H Location: Value from Column L Furnishing: Value from Column M *Size: Value from Super Built-up: (Value from Column O) & (Column N) / Built-up: (Value from Column P) & Unit : ( Column N) / Carpet: (Value from Column Q) & Unit : ( Column N) Floor Number: Value from Column R Total Floor: Value from Column S Age of the property: Value from Column T Rent: Value from Column U / (Column X) Deposit: Value from Column V Price: Value from Column W / (Column X) Property Description: Value from Column AA Proposal: Value from Column AB 7) Property Code: Value from Column C Property Type: Value from Column F Flat type: Value from Column G Area: Value from Column H Location: Value from Column L Furnishing: Value from Column M *Size: Value from Super Built-up: (Value from Column O) & (Column N) / Built-up: (Value from Column P) & Unit : ( Column N) / Carpet: (Value from Column Q) & Unit : ( Column N) Floor Number: Value from Column R Total Floor: Value from Column S Age of the property: Value from Column T Rent: Value from Column U / (Column X) Deposit: Value from Column V Price: Value from Column W / (Column X) Property Description: Value from Column AA Proposal: Value from Column AB 8) 9) 10) Continues *COMMERCIAL PROPERTIES ON *RENT 11) 12) 13) continues *COMMERCIAL PROPERTIES ON *SALE 14) 15) 16) continues *AGRICULTURE PROPERTIES ON *RENT 17) 18) 19) continues *AGRICULTURE PROPERTIES ON *SALE 20) 21) 22) continues Space for name and signature |
#2
|
||||
|
||||
Your description and data suggest a mailmerge from Word. No macros required. All you need do once you've created the mailmerge main document is to apply filtering there to restrict the output to the particular records you're interested in - filtering in Excel has no effect on a mailmerge.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel VBA Macro - Deleting Specific Data based on criteria | MD011 | Excel Programming | 3 | 12-10-2014 02:15 AM |
transpose values based on mulitple criteria | mlttkw | Excel | 1 | 12-06-2013 02:00 AM |
MACRO - Insert row based on Form Field Criteria | Elan05 | Word VBA | 5 | 04-16-2013 06:39 AM |
Change values in cells based on criteria | SaneMan | Excel Programming | 2 | 02-02-2012 07:58 AM |
Checking ref # status based on sheet2 ref #. | aligahk06 | Excel | 1 | 04-26-2010 11:22 PM |