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:Column “C”
Property Type:Column “F”
Flat type:Column “G”
Area:Column “H”
Location:

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