Forum Discussion

MarieParis's avatar
MarieParis
Copper Contributor
Nov 29, 2020
Solved

Transpose the table on VBA with missing values for the dates

Hello Experts, I'm novice in VBA. I'm trying to transpose my data:  I would like to have this result:    I tried all day the methodes like: Resize(UBound(Table2, 1), UBound(Table2, 2)...
excel
Formulas and Functions
Macros and VBA
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Nov 29, 2020

    MarieParis As said, it's difficult to help if I don't see the entire "picture". That is, all you want to achieve. The pivot table is created with the macro, and it's created in the cell you indicate.

     

    Now that the PT is in place, and if based on a structured table, you should not have to press the button anymore. And that is what I really meant earlier. You don't need a macro to do the transposing of your data. Create the PT once and refresh it (Data ribbon, Refresh All) when data is added to the table. The "transposed" table will be "recalculated" instantly. Should you want that step to be automated, then you can have a macro (and a button) that does the refreshing for you. But it would be practically the same as pressing the refresh button in the Data ribbon.

     

    I apologise if I'm not clear.

     

    (Picture taken on a Mac, but it looks similar on a Excel for Windows)

  • MarieParis's avatar
    MarieParis
    Copper Contributor
    Nov 29, 2020

    Thank you Riny_van_Eekelen, but I need a VBA solution, beacause I need to transpose this table by  cliking on the button (starting a macro programme).

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Nov 29, 2020

      MarieParis Okay, but why not record the creation of the pivot table? Clean-up the code a little and assign it to a button. The code below is almost as it was recorded. Cleaned it a little bit but I haven't done anything to make it more dynamic. Just to demonstrate that you can create a PT with a macro.

       

       

      Sub CreatePT()

    Range("A5").Select
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=6).CreatePivotTable TableDestination:="Feuil1!R18C6", _
        TableName:="PivotTable2", DefaultVersion:=6
    Sheets("Feuil1").Select

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
        .Orientation = xlColumnField
        .Position = 1
    End With

    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Amount"), "Sum of Amount", xlSum
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reference")
        .Orientation = xlRowField
        .Position = 1
    End With

    ActiveSheet.PivotTables("PivotTable2").RowGrand = False
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "Reference[All]", _
        xlLabelOnly + xlFirstRow, True

End Sub

       

       

      • MarieParis's avatar
        MarieParis
        Copper Contributor
        Nov 29, 2020

        Riny_van_Eekelen Thank you very much!

        I'm trying to adopt ypur code, but I had the errors: 

        I added the file with my real data in case you need. Do you know some function please to replace [E1442] by something like Last.Row ? Thank you

Resources