Copy transpose & merge Macro

  • I have a list of names, in a column, I would like to be able to run a macro to copy the list (names only), merge each name into 3 cells (each name is in one cell only at the moment) and transpose them so they are column headers.


    I'm sure I can figure out a way to do this but it would be very long winded and probabaly take me the best part of 24 hours, so I was hoping for some help, if there is a simple piece of code.


    Any help, as ever, greatly appreciated.


    Thanks


    Ray

    ________________________________________


    Good Luck!


    Ray :nana:

  • Not sure exactly what you are trying to do but this might work:

    Code
    Dim cell
    Dim strName As String
    Dim x As Integer
        For Each cell In Selection
            x = x + 1
            strName = cell & cell.Offset(0, 1) & cell.Offset(0, 2)
            Cells(1, x + 3) = strName
        Next
  • Thanks for the help, unfortunately it isn't exactly what I require.


    I need to transpose a list from a column to a row in another sheet at the same time insert two columns between each entry and then merge each entry with the 2 added columns.


    The SS attached shows what I need to do but as yet I can't figure out the code apart from the record MACRO which long term doesn't suffice.

  • Hi,


    try this



    hope it helps

  • Jindon, I am impressed, very nice code and it does the job exactly as I wanted.....Another thing learnt too!


    Thank you very much for this.


    Ray

    ________________________________________


    Good Luck!


    Ray :nana:

  • Jindon, could this code be improved slightly in that there may already be data in A1, B1 etc. so that the copy and paste starts after the data that is already there?


    I assume that the following code has something to do with this?



    With ws2.Range("1:1")
    .MergeCells = False
    .ClearContents

    If it does / doesn't could you please explain what this does...the .Range("1:1") part?


    Thanks again.


    Ray

    ________________________________________


    Good Luck!


    Ray :nana:

  • Hi,
    try this
    select the cell first then run this vba code
    regarding Test002:
    can you add following two lines just before End Sub
    Set ws1 = Nothing
    Set ws2 = Nothing

  • Jindon, thanks but I don't understand what you have asked me to do?


    I tried test003 and it doesn't work?


    I tried adding the WS1 Set to Nothing before end sub and that still overwrites any data that is already in the Row 1 of the Output WS.


    Thanks for your persistence with this!


    Ray

    ________________________________________


    Good Luck!


    Ray :nana:

  • Jindon, no problem I have mofified your code and it now passes over any data that is already in the 1st Row


    Thanjks again for your assistance with this. The issue appeared to be the deletion of anything that was already in ws2 so I have blanked that piece of code. Tried with and without resetting the ws on exit and works both ways.


    I assume the set to nothing on exit is to "close it off"?


    Thanks again for your help.


    Ray

    ________________________________________


    Good Luck!


    Ray :nana:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!