VBA code to change format of table

[KK3008]

Hi,

I am trying to change the format of this table (un-pivot)-

		A1	A1	A1		A2	A2	A2
YEAR	Floorset	Target	Instore	Newness		Target	Instore	Newness
FY20		100	89	25		200	193	55

<tbody>
</tbody>

To look like this-

Business	YEAR	Floorset	Target	Instore	Newness
A1	FY20		100	89	25
A2	FY20		200	193	55

<tbody>
</tbody>

Could someone help me with the VBA code for this please?

Thanks in advance!

 

[DanteAmor]

If your data is like this

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; ">A1</td><td style="background-color:#92d050; ">A1</td><td style="background-color:#92d050; ">A1</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; ">A2</td><td style="background-color:#92d050; ">A2</td><td style="background-color:#92d050; ">A2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >YEAR</td><td >Floorset</td><td >Target</td><td >Instore</td><td >Newness</td><td > </td><td >Target</td><td >Instore</td><td >Newness</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >FY20</td><td > </td><td style="text-align:right; ">100</td><td style="text-align:right; ">89</td><td style="text-align:right; ">25</td><td > </td><td style="text-align:right; ">200</td><td style="text-align:right; ">193</td><td style="text-align:right; ">55</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >FY21</td><td > </td><td style="text-align:right; ">101</td><td style="text-align:right; ">90</td><td style="text-align:right; ">26</td><td > </td><td style="text-align:right; ">201</td><td style="text-align:right; ">194</td><td style="text-align:right; ">56</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >FY22</td><td > </td><td style="text-align:right; ">102</td><td style="text-align:right; ">91</td><td style="text-align:right; ">27</td><td > </td><td style="text-align:right; ">202</td><td style="text-align:right; ">195</td><td style="text-align:right; ">57</td></tr></table>

The result will be like this
<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Business</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">YEAR</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Floorset</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Target</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Instore</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Newness</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A1</td><td >FY20</td><td > </td><td style="text-align:right; ">100</td><td style="text-align:right; ">89</td><td style="text-align:right; ">25</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A2</td><td >FY20</td><td > </td><td style="text-align:right; ">200</td><td style="text-align:right; ">193</td><td style="text-align:right; ">55</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A1</td><td >FY21</td><td > </td><td style="text-align:right; ">101</td><td style="text-align:right; ">90</td><td style="text-align:right; ">26</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A2</td><td >FY21</td><td > </td><td style="text-align:right; ">201</td><td style="text-align:right; ">194</td><td style="text-align:right; ">56</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >A1</td><td >FY22</td><td > </td><td style="text-align:right; ">102</td><td style="text-align:right; ">91</td><td style="text-align:right; ">27</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A2</td><td >FY22</td><td > </td><td style="text-align:right; ">202</td><td style="text-align:right; ">195</td><td style="text-align:right; ">57</td></tr></table>

Use this:

Sub change_format()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, k As Long, j As Long
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    sh2.Rows("2:" & Rows.Count).ClearContents
    k = 2
    For Each c In sh1.Range("A3", sh1.Range("A" & Rows.Count).End(xlUp))
        For j = 3 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
            sh2.Cells(k, "A").Value = sh1.Cells(1, j).Value
            sh2.Cells(k, "B").Value = c.Value
            sh2.Cells(k, "C").Value = c.Offset(0, 1).Value
            sh2.Cells(k, "D").Resize(1, 3).Value = sh1.Cells(c.Row, j).Resize(1, 3).Value
            k = k + 1
        Next
    Next
    MsgBox "End"
End Sub

 

[KK3008]

Hi Dante Amor, thank you so much!

How can the code be modified for such a modification in the table?-

Input table-

		A			B			C
STORE RECEIPT WEEK	Floorset	TARGET	In Store	Newness	Actual Places	TARGET	In Store	Newness	Actual Places	TARGET	In Store	Newness	Actual Places
Apr Wk1	APR PT 2 FLOORSET	100	89	25	100	100	89	25	100	100	89	25	100
Apr Wk2		101	89	26	101	101	89	26	101	101	89	26	101
Apr WK3		102	89	27	102	102	89	27	102	102	89	27	102
Apr WK4		103	89	28	103	103	89	28	103	103	89	28	103

<colgroup><col span="14"></colgroup><tbody>
</tbody>

Desired Output-

Business	STORE RECEIPT WEEK	Floorset	TARGET	In Store	Newness	Actual Places
A	Apr Wk1	APR PT 2 FLOORSET	100	89	25	100
	Apr Wk2		101	89	26	101
	Apr WK3		102	89	27	102
	Apr WK4		103	89	28	103
B	Apr Wk1	APR PT 2 FLOORSET	100	89	25	100
	Apr Wk2		101	89	26	101
	Apr WK3		102	89	27	102
	Apr WK4		103	89	28	103
C	Apr Wk1	APR PT 2 FLOORSET	100	89	25	100
	Apr Wk2		101	89	26	101
	Apr WK3		102	89	27	102
	Apr WK4		103	89	28	103

<colgroup><col width="64" span="7" style="width: 48pt;"></colgroup><tbody>
</tbody>

Any help would be great.

Thank you!