Nov 29 2020 03:15 AM
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)) = Table2, Application.transpose(Tbl1) but I don't have the diserid result. Could you help me please?
The vesrion of my Excel is: 365MSO
I attached herewith the file with my data.
Thank you very much!
Nov 29 2020 03:39 AM
@MarieParis Rather than VBA, perhaps a Pivot Table will work for you. See attached.
Nov 29 2020 03:46 AM
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).
Nov 29 2020 04:01 AM - edited Nov 29 2020 04:04 AM
@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
Nov 29 2020 05:05 AM
@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
Nov 29 2020 05:40 AM
@MarieParis The code I recorded builds a pivot table that has a structured table as its source. Structured tables expand automatically and when you create a pivot table, you can refer to the table name. Then there is no need to go look for the last cell in the data range. The data in the file you uploaded is not structured. It has no column headers and it has a blank row. I would be surprised if my code would work on this data set at all. But, I can't be sure until I see the code below the Error message-box.
Nov 29 2020 07:02 AM
@Riny_van_Eekelen Thank you! It's works!
I have the last question, please:
I run first time my macro: my table is created
When I run the second time: I have the error message beacause my table is already exist and can't overwrite it.
How to put an alerte message and stop the macro?
Nov 29 2020 07:26 AM
Solution@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)
Nov 29 2020 07:39 AM
@Riny_van_Eekelen Soory, I will explain you the fonctionality of my macro:
Step 1: I'm connecting to my row data, pressing on the button, and I have my data to transpose. This data can evolve, so I must refresh it once a week:
Step 2: I transpose this data: I added the macro of pivoting to the macro of the button.
When I click to the button I make the data query and pivoting.
Nov 29 2020 07:49 AM
Nov 29 2020 07:53 AM
@MarieParis Great! Always good to hear that it works.
Nov 29 2020 07:26 AM
Solution@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)