+ Reply to Thread
Results 1 to 5 of 5

Need Help w/Macro that Insert Rows, Copy Row, and Transpose to Column

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2007
    Posts
    2

    Need Help w/Macro that Insert Rows, Copy Row, and Transpose to Column

    Hello,

    This is my first time asking for help and I am a complete noob at VBA, although I would love to learn.

    I've looked through a bunch of other posts related to my issue but I couldn't find exactly the same issue and had to give up.

    I've attached a before & after file for reference but what I'm trying to do is the following.

    I have a sales database with basically the following layout

    Prod Factor1 Factor2 10'Sales 11'Sales 12'Sales
    1 xx xx 5 10 15
    2 yy yy 10 5 20

    What I want is to add a Year and Sales column and transpose to look like the following.

    Prod Factor1 Factor2 Year Sales
    1 xx xx 2010 5
    1 xx xx 2011 10
    1 xx xx 2012 15
    2 yy yy 2010 10
    2 yy yy 2011 5
    2 yy yy 2012 20

    I have about 5000 products, in which each would need 13 lines inserted for 2000-2012.
    Then copy and transpose year & sales.

    I really appreciate all your help in advance!

    Thank you,

    Test.xlsm

  2. #2
    Forum Contributor
    Join Date
    03-21-2012
    Location
    Ho Chi Minh city
    MS-Off Ver
    Excel 2003
    Posts
    180

    Re: Hope this help!

    PHP Code: 
    Option Explicit
    Sub Hiroki3296
    ()
     
    Dim Rws As LongjJ As LongwW As Byte
     
     Rws 
    = [B2].CurrentRegion.Rows.Count
     ReDim Arr
    (Rws1 To 5)
     
    Arr(21) = [A1].Value:        Arr(22) = [B1].Value
     Arr
    (23) = [c1].Value:        Arr(24) = "Year"
     
    Arr(25) = "Sales"
     
    For jJ 2 To Rws
        
    For wW 1 To 3
            Arr
    (jJ wW1) = Cells(jJ"A").Value
            Arr
    (jJ wW2) = Cells(jJ"B").Value
            Arr
    (jJ wW3) = Cells(jJ"C").Value
            Arr
    (jJ wW4) = 2009 wW
            Arr
    (jJ wW5) = Cells(jJ"C").Offset(, wW).Value
        Next wW
     Next jJ
     
    [o1].Resize(Rws5).Value Arr()
    End Sub 

  3. #3
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Need Help w/Macro that Insert Rows, Copy Row, and Transpose to Column

    hiroki3296,

    Welcome to the Excel Forum.


    Thanks for the sample workbook.


    With your raw data in worksheet Sheet1, a new worksheet Results will be created with the re-formatted data.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Please Login or Register  to view this content.

    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


    Then run the ReorgData macro.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  4. #4
    Registered User
    Join Date
    08-27-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Need Help w/Macro that Insert Rows, Copy Row, and Transpose to Column

    Stan,

    You are amazing! The macro worked perfectly!

    So for future reference, if I had to add/remove columns, could you please explain which portion I would need to change?

    or is it not that easy?

    Thanks again,

    Hiro

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Need Help w/Macro that Insert Rows, Copy Row, and Transpose to Column

    hiroki3296,

    So for future reference, if I had to add/remove columns, could you please explain which portion I would need to change?
    See if the bold lines of code will help you to understand better what is going on in the macro.


    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