It would be really nice if Tab just released the code as an xlsm as well so we could deconstruct it
1 of 1 people found this helpful
With the help of an Excel VBA expert who did some of the ground work, I modified his approach to simplify it. The following statement can replace the "Application.Run 'UnpivotData'" command:
- Run "tsiUnpivot", ActiveSheet, ActiveCell, ActiveSheet.Range(ActiveCell, Cells(ActiveCell.CurrentRegion.Rows(ActiveCell.CurrentRegion.Rows.Count).Row, ActiveCell.CurrentRegion.Columns(ActiveCell.CurrentRegion.Columns.Count).Column)), ActiveCell.CurrentRegion, "", 0, False
This replicates the selection of the current cell as the first data cell and leaving the optional column titles empty, which is the default values in the dialog itself.
Feel free to play with this, or hit me up with questions. It took a few hours of investigation and trial and effort to succeed. Hopefully it saves other people some time!