Macro Wanted VBA Table to List with more than 3 columns

[carpediem_3112]

Howdy,


I have a table that shows a set of possible combinations, but in order to actually use it, I need to make it a list.


The table is like

And the wanted result is like

Which means,
FIRST: Several columns (SKU, CODE, DESCRIPTION), making a "three column row"
THEN: If you have a number 1-one, you make de combination of the column TITLE (red) with the "three column row" PLUS the second column TITLE (blue)


Which means that each table ROW, can make up to 4 LIST rows in this example (1:4).


Real life, I have "six column row" 9 reds (and growing) and 16 blues (and growing), giving a good 1:144 :-S


So far, I've found how to make, from the origin table a 3 column list (NOT WANTED)

(NOT WANTED)


The above (not wanted) result came from Convert Excel Tables To Lists « The Universe Divided

Sub Table2List1()
'
' Table2List1 Macro
' Catalogo
'


''http://michiel.wordpress.com/2009/03/12/convert-excel-tables-to-lists/


''Before we start, we check some preconditions.
''We have to make sure that we are inside a set of data, formed into a table.
''All we do is just check if we have at least two rows and two columns (not the ultimate, but it works).
If ActiveCell.CurrentRegion.Rows.Count < 2 Then
Exit Sub
End If
If ActiveCell.CurrentRegion.Columns.Count < 2 Then
Exit Sub
End If


''Then we will need some variables to refer to the various sections of the table
Dim table As Range
Dim rngColHead As Range
Dim rngRowHead As Range
Dim rngData As Range
Dim rngCurrCell As Range


''Next. we will need some variables for the data itself
Dim rowVal As Variant
Dim colVal As Variant
Dim val As Variant




''Now, we will start pointing our variables to the data, row headings and column headings, like so
Set table = ActiveCell.CurrentRegion
Set rngColHead = table.Rows(1)
Set rngRowHead = table.Columns(1)
Set rngData = table.Offset(1, 1)
Set rngData = rngData.Resize(rngData.Rows.Count - 1, rngData.Columns.Count - 1)
''Note that “currentregion” is a handy tool that expands any cell into a surrounding of non-empty cells.
''So this way your selected cell could be anywhere inside the table when you run the macro.
''The data part is a bit harder, line 4 and 5
''$$$Set rngData = table.Offset(1, 1)   Set rngData = rngData.Resize(rngData.Rows.Count - 1, rngData.Columns.Count - 1)$$$
''together shift and resize the original table to form the right bottom part, where all the data resides.




''Next, we create a new sheet in the workbook, to hold the list.
ActiveWorkbook.Worksheets.Add


''In this sheet, we create a first row, “manually”, where we name the column headings for our list.
''These column headings are very important for sorting, analysis, pivot tables, export and such.
''The last statement instantly moves the current cell selection one row down.
''Notice we’re inserting a special column for Row Number.
''This is not always necessary, but it doesn’t hurt, and it helps you to always be able to restore the original order of the list.
ActiveCell.Value = "Row#"
ActiveCell.Offset(0, 1).Value = "RowValue"
ActiveCell.Offset(0, 2).Value = "ColValue"
ActiveCell.Offset(0, 3).Value = "Data"
ActiveCell.Offset(1, 0).Select


''Now it’s time for the actual grunt work, looping through the table
Dim n As Long
For Each rngCurrCell In rngData
colVal = rngColHead.Cells(rngCurrCell.Column - table.Column + 1)
rowVal = rngRowHead.Cells(rngCurrCell.Row - table.Row + 1)
''The “for each rngCurrCell in” is a real beauty in VBA.
''It just runs through any selection, without worries of overflows, row and column numbers, or calculations.
''In the loop, we set the value of the current column and row.
''Note that the rngCurrCell.column and rngCurrCell.row are not relative, it’s the actual number of the column/row.
''So if the tables starts at C3, the first cel is having column=3 and row=3.




''Here, we upped counter ‘n’ and put it in the list.
n = n + 1
ActiveCell.Value = n


''We do the same trick again to put a new row in the data list on our new sheet.
''As you can see this part of code is repeated from the part where we created the header.
''A small improvement would be to create a function named ‘newRow(n, rv, cv, dv)’ to insert a new row with these values.
ActiveCell.Offset(0, 1).Value = rowVal
ActiveCell.Offset(0, 2).Value = colVal
ActiveCell.Offset(0, 3).Value = rngCurrCell.Value
ActiveCell.Offset(1, 0).Select


''If, instead of actual values, you prefer to link to the original cell, you can use
''(quitar el '' para que funcione) ActiveCell.Offset(0, 3).Value = "=" & rngCurrCell.Worksheet.Name & "!" & rngCurrCell.Address






Next
End Sub

You can find a sample XLS here Download Table To List Macro Wanted.xls from Sendspace.com - send big files the easy way




Any ideas??

 

[carpediem_3112]

Got the solution here!!

[SOLVED] Macro Wanted VBA Table to List with more than 3 columns