Copying rows of data to another worksheet without uniform row data Excel 2010
I have had some experience with macros and VBA a few years ago, however, I am having trouble writing a macro to copy rows of data to specified worksheets from a master sheet.
I want to sort mortgage data provided by my company's database. When imported into Excel, I get data in columns A through AJ. I would like to sort the rows based on the header Current Phase in column Y.
(No Status(no text provided in column Y, "Pre-Qualification", and All Others ("Application", "Submitted to Processing", "Accepted by Processing", "Submitted", "Received in Underwriting", "Suspended", "Declined", "Withdrawn", "Approved with Conditions", "Clear to Close", "Closed", "Warehoused", "Shipped", "Funded")).
My problem comes into play here because the borrower's name is in column B and their address is in column C. For the most part the address is written in 2 rows, but sometimes there is no address provided in 2 rows (same row as name and all other data, and the row below name and all other data) and other times there is no address provided in 1 row (same as the name and all other data).
Can anyone provide some macro help with copying either the 1 or 2 rows that provide the borrower's information to the desired sheets based on the the information in column Y?
What I would suggest you is something we do often at work.
- Have a master sheet where you will enter every possible combination of text with the appropriate corresponding sheet name in the cell next to it.
- Name both ranges.
- Add a new column in the data tab (in column AK) with a formula like: =index(SHEET_NAMES, equiv($Y2, STATUSES)) This will find the correspondent sheet name to the line of data in Y2 (assuming you have headers in row 1)
Finally, just loop from the beginning of your data range until data is "" (empty) and put data in the tab name found in AK.
Hope this helps.