Results 1 to 2 of 2

Thread: VBA Copy and Paste Data to New Sheet

  1. #1
    Neophyte Johnathannn's Avatar
    Join Date
    Oct 2019
    Posts
    1
    Articles
    0
    Excel Version
    2013

    VBA Copy and Paste Data to New Sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,


    I am looking for VB code to copy data from one sheet, and paste it onto another sheet in a different format.


    Here is the current format:


    ID Amount 2014 Amount 2015 Amount 2016 Amount 2017 Amount 2018
    001 2066.1 4438.73 6703.19 10705.06 5785.5
    003 1166.12 2985.56 6071.15 8415.4 4548.04
    187 779.83 661.34 0 0 0
    010 1226.74 3155.46 6352.5 8693.07 5027.26
    006 0 0 0 2543.51 3297.14
    011 181.28 702.5 4463.38 6080.35 2715.44
    007 1170.04 2952.28 5011.27 8757.77 4548.04
    032 734.12 325.95 3220.54 7080.43 3156.4


    Here is the desired format (on a new sheet):
    ID Years Amount
    001 Amount 2014 2066.1
    001 Amount 2015 4438.73
    001 Amount 2016 6703.19
    001 Amount 2017 10705.06
    001 Amount 2018 5785.5
    003 Amount 2014 1166.12
    003 Amount 2015 2985.56
    003 Amount 2016 6071.15
    003 Amount 2017 8415.4
    003 Amount 2018 4548.04
    187 Amount 2014 779.83
    187 Amount 2015 661.34
    187 Amount 2016 0
    187 Amount 2017 0
    187 Amount 2018 0
    010 Amount 2014 1226.74
    010 Amount 2015 3155.46
    010 Amount 2016 6352.5
    010 Amount 2017 8693.07
    010 Amount 2018 5027.26
    006 Amount 2014 0
    006 Amount 2015 0
    006 Amount 2016 0
    006 Amount 2017 2543.51
    006 Amount 2018 3297.14
    011 Amount 2014 181.28
    011 Amount 2015 702.5
    011 Amount 2016 4463.38
    011 Amount 2017 6080.35
    011 Amount 2018 2715.44
    007 Amount 2014 1170.04
    007 Amount 2015 2952.28
    007 Amount 2016 5011.27
    007 Amount 2017 8757.77
    007 Amount 2018 4548.04
    032 Amount 2014 734.12
    032 Amount 2015 325.95
    032 Amount 2016 3220.54
    032 Amount 2017 7080.43
    032 Amount 2018 3156.4


    Thanks

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    194
    Articles
    0
    Excel Version
    2019
    This is a simple task for Power Query/Get and Transform. Here is the Mcode for that action. Load to PQ, and Unpivot all but the first column

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Amount 2014", type number}, {"Amount 2015", type number}, {"Amount 2016", type number}, {"Amount 2017", type number}, {"Amount 2018", type number}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value")
    in
        #"Unpivoted Other Columns"
    Attached is the file for your review
    Attached Files Attached Files

Posting Permissions

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