+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    Orlando, FLorida
    MS-Off Ver
    Excel 2010
    Posts
    16

    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!

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    Create a workbook with a BEFORE and AFTER example of all this.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-14-2013
    Location
    Orlando, FLorida
    MS-Off Ver
    Excel 2010
    Posts
    16

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

    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!
    Attached Files Attached Files

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    This should do it, just make sure the sheet is onscreen before you start. To be clear, we are only splitting the cells in columns V and W. Your example showed the two cells as having the same number of lines in the text, but I presumed that to not always be true, so this should split down properly no matter how many lines of text are in each cell.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-14-2013
    Location
    Orlando, FLorida
    MS-Off Ver
    Excel 2010
    Posts
    16

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

    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?

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    Try it on the sample doc you uploaded, it does work on that one.

    So, examine columns V and W. How is your real doc different in those columns?

    The macro also looks UP column B at the beginning to see how many rows are used. Does your real sheet have data in column B the same way your sample did?

    If you can't figure out what is different, post up a copy of your actual sheet, desensitize it, but make as few changes as needed to accomplish that. ZIP it up if needed.

  7. #7
    Registered User
    Join Date
    05-14-2013
    Location
    Orlando, FLorida
    MS-Off Ver
    Excel 2010
    Posts
    16

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

    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!

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Thanks.

  9. #9
    Registered User
    Join Date
    05-14-2013
    Location
    Orlando, FLorida
    MS-Off Ver
    Excel 2010
    Posts
    16

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

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

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    Did you have a question to go with these updated files?

  11. #11
    Registered User
    Join Date
    05-14-2013
    Location
    Orlando, FLorida
    MS-Off Ver
    Excel 2010
    Posts
    16

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

    Yes, I did. Sorry about my misunderstanding. I am trying to add to the macro to allow for columns A-T to populate in the empty spaces of the new rows added accordingly as well as also allow for the step numbers to populate as well. Any help is appreciated and my apologies again. Thanks!
    Last edited by diaphanous1138; 05-22-2013 at 12:37 PM.

  12. #12
    Registered User
    Join Date
    05-14-2013
    Location
    Orlando, FLorida
    MS-Off Ver
    Excel 2010
    Posts
    16

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1