+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    03-21-2016
    Location
    Peterborough, England
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    Hi, I'm trying to find a way to automate what is currently a very labour intensive process. I have a spread sheet where one cell has comma separated values. What I'd like the macro to do is separate out these values, then insert the required number of rows and transpose these separated values in to these rows.

    The number of CSVs in this fixed column (column D) will alter from row to row, some might only have a single value in so nothing would need doing to those rows.

    Example spread sheets are attached - a before, an after and an ideal after. Just delivering the "after" will be fine unless it's super easy to deliver the "ideal after". The CSV's in column D will not be consecutive in the actual live data or made up solely of numbers. Some might be text only, numbers only or a mixture of the two (if that makes any difference).

    Any help would be hugely appreciated! Thanks!
    Attached Files Attached Files
    Last edited by Schern; 03-21-2016 at 10:41 AM. Reason: Issue solved

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

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

    You attached three CSV files and not example spreadsheets.

    It's not clear to me from your description and the the three CSV files what you are starting with and what result you want.

    Perhaps it would be better if you attached one excel file with three sheets.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    03-21-2016
    Location
    Peterborough, England
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    Hi AlphaFrog - thanks very much for your response. Please find the excel file attached as requested.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

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

    Try this...

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

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

    For the csv file provided.

    I can not post my code due to bloody Scuri...
    Attached Files Attached Files
    Last edited by jindon; 03-21-2016 at 10:45 AM.

  6. #6
    Registered User
    Join Date
    03-21-2016
    Location
    Peterborough, England
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    You are absolutely amazing - thanks very much!

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

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

    Quote Originally Posted by Schern View Post
    You are absolutely amazing - thanks very much!
    You're welcome. Thanks for the feedback.

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

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

    Hi there,

    See if the following code does what you need:

    Please Login or Register  to view this content.
    The highlighted value may be changed to suit your requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. vba code for Transpose past special from col to rows- Insert serial No
    By srinivasan1965 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2016, 12:26 AM
  2. [SOLVED] Macro to Insert New Row, Copy Down Data, Paste Text to Columns Down
    By lasjbp9 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2013, 09:33 AM
  3. [SOLVED] Macro with text to columns, insert rows, and paste special>transpose
    By diaphanous1138 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-22-2013, 02:49 PM
  4. [SOLVED] Cycle Through A Series of Worksheets & Insert Rows, Then Special Paste Data
    By DDM64 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-26-2012, 10:45 PM
  5. Replies: 1
    Last Post: 08-12-2012, 04:02 PM
  6. Replies: 2
    Last Post: 02-19-2007, 04:53 PM
  7. [SOLVED] Macro for one-click paste special transpose
    By Victoria @ WB in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-22-2005, 09:05 PM

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