Macro with text to columns, insert rows, and paste special>transpose

I am looking for help creating a macro that will run text to columns on each row, paste the results at the end of the row, then insert new rows directly beneath that match the count of the additional items pasted with text to columns (example: After text to columns 5 columns are made. I need to insert 4 rows below).

I then need this to select the current cell I used text to columns on and the new cells beneath it and then paste special>transpose the data that was creted above in text to columns. I thne need this to repeat for the next column over on all counts and then proceed down the list of rows until it is completed.

Any assitance or guidance on this would be appreciated. I am very novice at this and usually record macros. I have managed to cobble together a few based on recording and thigns i have found online but this one is a bit over my head on how to set it up so that it will perform all these functions and include all the variables needed for proceeding down the spreadsheet.

Thanks!

I have attached the two sample files with just a quick example of what I am trying to accomplish for a list of many records. Please let me know if you need anything else or have any questions.

Thanks again!

Thanks Jerry! I really appreciate the assistance. Ok, so I copied the code and entered it into the module in VBA for Excel. When I click to run it flashing (running) really quickly on the bar and then that's it. Nothing changes within the document. Is there something I am missing?

Thanks Jerry! Column B was the issue. I was missing the date and once I entered it the macro ran like a charm. I cannot thank you enough for your help with this. You just saved days of work for myself and my team. Thank you again so much!

Here are the updated files for your review. Any help on this is greatly appreciated. Thanks!

Ok, so I managed to put together a code that works even though I am sure it is the long way around. THe only thing I cannot figure out is how to make the U column number the empty rows until the next row with data is found and then restart all the way to the end. Any suggestions would be great. Thanks!

Please Login or Register  to view this content.