Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-19-2014, 10:31 AM
winmaxservices2 winmaxservices2 is offline need VBA to Transpose the Data from excel to word based on given criteria(status) Windows 8 need VBA to Transpose the Data from excel to word based on given criteria(status) Office 2013
Novice
need VBA to Transpose the Data from excel to word based on given criteria(status)
 
Join Date: Dec 2014
Posts: 2
winmaxservices2 is on a distinguished road
Default 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
Attached Files
File Type: xlsx Transpose from excel to word.xlsx (17.3 KB, 3 views)
File Type: docx Example..docx (18.5 KB, 3 views)
Reply With Quote
  #2  
Old 12-19-2014, 10:21 PM
macropod's Avatar
macropod macropod is offline need VBA to Transpose the Data from excel to word based on given criteria(status) Windows 7 64bit need VBA to Transpose the Data from excel to word based on given criteria(status) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
need VBA to Transpose the Data from excel to word based on given criteria(status) 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
need VBA to Transpose the Data from excel to word based on given criteria(status) Checking ref # status based on sheet2 ref #. aligahk06 Excel 1 04-26-2010 11:22 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 12:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft