VBA to transpose dataset

[zach9208]

Hello,

I am looking for help with a macro to change the format of a file to transpose data so it is going down versus going across. I need this to be a loop for each record I have.

The following link provides an image of dummy data both the before and after formatting https://ibb.co/fv4grR . As you can see, I need the EST_MND_USD_AMT and ANULZ_RVNU_AMT fields to be going down instead of across. the values in F2:J2 are the values that need to be repeated for each record in the EST_FNDG_QTR_NM column. I also need the data to be copied down for each of the 5 new rows created for each quarter. I am okay with moving these to a different tab if needed. The current tab is called "Global Funds".


Let me know if you need any clarifications. Thanks in advance for the help!!

 

[ParamRay]

Hello, you can use the unpivot feature of Power Query to achieve this...

 

[zach9208]

I do not think I can use this for the project I am working on. I am working on a macro that is cleaning up template files that need to be in a specific format to load into a database. Therefore, I have to repeat this function on 20+ workbooks per cycle. I really need to use VBA. Here is what I have thus far (I know it is rough code), just trying to make due.

Sub UpdateGlobalFunds2()


    Dim DSht As Worksheet
    Dim Rng As Range
    Dim Fll As Long
    
Application.ScreenUpdating = False


Worksheets.Add().Name = "DatabaseFormat"
'Range("H3:T3").Select 'Moves quarter labels down one row
   ' Selection.Cut
   ' Range("H4").Select
   ' ActiveSheet.Paste
        


    Set DSht = Sheets("DatabaseFormat")
    
    With Sheets("Global Funds")
        For Each Rng In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
            Rng.Resize(, 7).Copy DSht.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(6, 7)
            Rng.Offset(, 7).Resize(, 6).Copy
            DSht.Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True
        Next Rng
    End With
    
    
    
    Columns("A:B").Delete Shift:=xlToLeft
    
    Columns("F:F").Insert Shift:=xlToRight
    
    Range("A1").Value = "CALC_BPS_AMT"
    Range("B1").Value = "PRMY_PRDCT_NM"
    Range("C1").Value = "BTQ_NM"
    Range("D1").Value = "MJR_INV_VCHL_NM"
    Range("E1").Value = "PTF_CD"
    Range("F1").Value = "EST_FDNG_QTR_NM"
    Range("F2").Value = "=Index!$A$6"
    Range("F3").Value = "=Index!$A$7"
    Range("F4").Value = "=Index!$A$8"
    Range("F5").Value = "=Index!$A$9"
    Range("F6").Value = "=Index!$A$10"
    Range("F7").Value = "=Index!$A$11"
    Range("G1").Value = "EST_MNDT_USD_AMT"
    
 With DSht
    Fll = Range("B" & Rows.Count).End(xlUp).Row - 6
        Range("F2:F7").Copy Range("F8").Resize(Fll)
 End With


Application.CutCopyMode = False
End Sub

Hello, you can use the unpivot feature of Power Query to achieve this...

I am having issues getting the following code copying my range of F2:F7, to the end of the data. For some reason it only does this one more time.

With DSht
    Fll = Range("B" & Rows.Count).End(xlUp).Row - 6
        Range("F2:F7").Copy Range("F8").Resize(Fll)
 End With

Lastly, I still have not figured out how to pivot the ANULZ_RVNU_AMT columns without doubling up the number of rows with the first set of pivoted rows for EST_MND_USD_AMT.

 

[zach9208]

@Fluff

Maybe you have some ideas, you helped me with a macro similar to this? I was working off the code you provided in the following post.

https://www.mrexcel.com/forum/excel-questions/1021127-loop-copy-each-row-paste-new-tab-4-times.html