Macro help: Transpose row to column & Copy, paste data
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Macro help: Transpose row to column & Copy, paste data

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro help: Transpose row to column & Copy, paste data

    Hi, i need some help. But not sure if someone able to help me.
    I using excel 2010.

    This is how my data is


    The Macro will Transpose COL E (Font format had to retain (like colour, bold or underline)


    Once transpose, marco will copy and paste the main row data and fill up the blank.

    (like u see in the pic, which highlighted in yellow are filled up base on the main row.)


    will appreciate someone could help me. thanks million

  2. #2
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,098
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    The reverse pivot table does this:

    https://www.youtube.com/watch?v=xmqTN0X-AgY

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    No it diff, pivot table can't fill up the column which is blank which you see on 2nd pic.

    Pivot table is reverse the whole set of data which already exist but my is based on column E

    I want the script to transpose base on column E and copy paste and fill up the blank base on the main row.

    The last pic is the final result.
    Last edited by harky; Jun 28th, 2018 at 02:35 PM.

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    Try this:-
    Code:
    Sub MG28Jun44
    Dim Lst As Long, Lstcol As Long, n As Long
    Application.ScreenUpdating = False
    Lst = Range("A" & Rows.Count).End(xlUp).Row
    For n = Lst To 2 Step -1
        Lstcol = Cells(n, Columns.Count).End(xlToLeft).Column - 5
        With Cells(n, 1).EntireRow
            .Copy
            Cells(n + 1, 1).Resize(Lstcol).EntireRow.Insert shift:=xlDown
            Cells(n, 6).Resize(, Lstcol).Copy
            Cells(n + 1, 5).PasteSpecial Transpose:=True
    End With
    Next n
    Columns("F:M").EntireColumn.Delete
    Application.ScreenUpdating = True
    End Sub
    Regards Mick

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    Thanks Mick.
    This works base on my image1 data, but i had issue here. I notice it cannot ignore if there is data like this.

    row 3 (if there is data on Col E onward, it will need to Transpose, copy & paste. if there is only 1 data on col E, thn ignore, no copy paste needed)


    Quote Originally Posted by MickG View Post
    Try this:-
    Code:
    Sub MG28Jun44
    Dim Lst As Long, Lstcol As Long, n As Long
    Application.ScreenUpdating = False
    Lst = Range("A" & Rows.Count).End(xlUp).Row
    For n = Lst To 2 Step -1
        Lstcol = Cells(n, Columns.Count).End(xlToLeft).Column - 5
        With Cells(n, 1).EntireRow
            .Copy
            Cells(n + 1, 1).Resize(Lstcol).EntireRow.Insert shift:=xlDown
            Cells(n, 6).Resize(, Lstcol).Copy
            Cells(n + 1, 5).PasteSpecial Transpose:=True
    End With
    Next n
    Columns("F:M").EntireColumn.Delete
    Application.ScreenUpdating = True
    End Sub
    Regards Mick

  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    Try this:-
    Code:
    Sub MG29Jun29
    Dim Lst As Long, Lstcol As Long, n As Long
    Application.ScreenUpdating = False
    Lst = Range("A" & Rows.Count).End(xlUp).Row
    For n = Lst To 2 Step -1
        Lstcol = Cells(n, Columns.Count).End(xlToLeft).Column - 5
        With Cells(n, 1).EntireRow
            If Lstcol > 0 Then
                .Copy
                Cells(n + 1, 1).Resize(Lstcol).EntireRow.Insert shift:=xlDown
                Cells(n, 6).Resize(, Lstcol).Copy
                Cells(n + 1, 5).PasteSpecial Transpose:=True
            End If
    End With
    Next n
    Columns("F:M").EntireColumn.Delete
    Application.ScreenUpdating = True
    End Sub
    Regards Mick

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    Sorry for late reply. Mick it works great.
    but i notice this line.

    Columns("F:M").EntireColumn.Delete

    So this will delete Col F to M.
    Possible to delete anyhow after Col E? as my last col may not be M


    Quote Originally Posted by MickG View Post
    Try this:-
    Code:
    Sub MG29Jun29
    Dim Lst As Long, Lstcol As Long, n As Long
    Application.ScreenUpdating = False
    Lst = Range("A" & Rows.Count).End(xlUp).Row
    For n = Lst To 2 Step -1
        Lstcol = Cells(n, Columns.Count).End(xlToLeft).Column - 5
        With Cells(n, 1).EntireRow
            If Lstcol > 0 Then
                .Copy
                Cells(n + 1, 1).Resize(Lstcol).EntireRow.Insert shift:=xlDown
                Cells(n, 6).Resize(, Lstcol).Copy
                Cells(n + 1, 5).PasteSpecial Transpose:=True
            End If
    End With
    Next n
    Columns("F:M").EntireColumn.Delete
    Application.ScreenUpdating = True
    End Sub
    Regards Mick
    Last edited by harky; Jul 1st, 2018 at 08:42 PM.

  8. #8
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,834
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    You're welcome
    You can change that line to any column you like to care of excess data.

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    oh ok. thank u so much!

    Quote Originally Posted by MickG View Post
    You're welcome
    You can change that line to any column you like to care of excess data.

  10. #10
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro help: Transpose row to column & Copy, paste data

    hmm

    i add one more column.
    So now is not column E but F
    and start off at A4

    how to edit the code

    Quote Originally Posted by MickG View Post
    You're welcome
    You can change that line to any column you like to care of excess data.
    Last edited by harky; Aug 20th, 2019 at 02:14 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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