0

I regularly get data in a pre-crosstabbed format that I want to unpivot. I've used the built in feature in Excel 2016 to generate most of the macro code, but I need to make it dynamic.

Each week, the columns headings will be the previous weeks dates and the macro has hard coded that specific set of dates used when recording the marco.

Here is my initial spreadsheet Initial data

The recorded macro gives the following:

    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & _
    "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Code"", Int64.Type}, {""Product Description"", type text}, {""Store Number"", Int64.Type}, {""Store Name"", type text}, {""04/09/2016"", Int64.Type}, {""05/09/2016"", Int64.Type}, {""06/09/2016"", Int64.Type}, {""07/09/2016" & _
    """, Int64.Type}, {""08/09/2016"", Int64.Type}, {""09/09/2016"", Int64.Type}, {""10/09/2016"", Int64.Type}})," & Chr(13) & "" & Chr(10) & _
    "    #""Unpivoted Columns"" = Table.UnpivotOtherColumns(#""Changed Type"", {""Code"", ""Product Description"", ""Store Number"", ""Store Name""}, ""Attribute"", ""Value"")" & _
    Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Unpivoted Columns"""

I've altered it to look like this, but it's not worked... 

Dim col1 As String, col2 as String, col3 as String, col4 as String, col5 as String, col6 as String, col7 as String

col1 = Range("E1").Value
col2 = Range("F1").Value
col3 = Range("G1").Value
col4 = Range("H1").Value
col5 = Range("I1").Value
col6 = Range("J1").Value
col7 = Range("K1").Value

ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & _
    "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Code"", Int64.Type}, {""Product Description"", type text}, {""Store Number"", Int64.Type}, {""Store Name"", type text}, {" & col1 & ", Int64.Type}, {" & col2 & ", Int64.Type}, {" & col3 & ", Int64.Type}, {" & col4 & _
    """, Int64.Type}, {" & col5 & ", Int64.Type}, {" & col6 & ", Int64.Type}, {" & col7 & ", Int64.Type}})," & Chr(13) & "" & Chr(10) & _
    "    #""Unpivoted Columns"" = Table.UnpivotOtherColumns(#""Changed Type"", {""Code"", ""Product Description"", ""Store Number"", ""Store Name""}, ""Attribute"", ""Value"")" & _
    Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Unpivoted Columns"""
Slai
  • 22,144
  • 5
  • 45
  • 53
ExplodingPony
  • 57
  • 7
  • 1
    I'm not close enough to the problem. What does your code do and what do you want it to do? How has it not worked? – BaldEagle Oct 12 '16 at 12:26
  • might be easier with the Power Query Unpivot, and then just refresh when the new data comes. – Slai Oct 12 '16 at 13:13
  • https://stackoverflow.com/questions/36365839/excel-macrovba-to-transpose-multiple-columns-to-multiple-rows/36366394#36366394 – Tim Williams Oct 12 '16 at 21:18
  • thanks for the help... figured it. took me a while, been many years since I last wrote any VBA – ExplodingPony Oct 22 '16 at 22:01

0 Answers0