VB Code to arrange data in Table format

Hello:

Please refer to attached file.
I have data as shown in sheet.
I need VB Code to rearrange these data in Table format as shown from Row 21 down for all stores.
I have manually done for one product ID 22123.


Each item in column A Cell A3 thru A12 has 2 rows
Row 3,5,7,9 and 11 has Qty Sold
Row 4,6,8,10 and 12 has Qty on Hand.
Each Item has ProdID in column C as shown.
Each store has data for size shown in row 2.
Store Name is in row1.


Let me know if you have any questions.
Thanks.

Riz

Add this code to your workbook and it will do this for you.

Sub CreateDataTable()

    Dim i, j As Integer
    Dim Row As Long
    Dim StoreColumn As Long
    
    Row = 21
    
    For i = 3 To 11 Step 2
        For j = 4 To 52
            StoreColumn = Int((j - 4) / 7) * 7 + 3
            Sheet1.Range("A" & Row).Value2 = Sheet1.Range("A" & i).Value2
            Sheet1.Range("B" & Row).Value2 = Sheet1.Range("C" & i).Value2
            Sheet1.Range("C" & Row).Value2 = Sheet1.Cells(1, StoreColumn).Value2
            Sheet1.Range("D" & Row).Value2 = Sheet1.Cells(2, j).Value2
            Sheet1.Range("E" & Row).Value2 = Sheet1.Cells(i, j).Value2
            Sheet1.Range("F" & Row).Value2 = Sheet1.Cells(i + 1, j).Value2
            Row = Row + 1
        Next j
    Next i
        
End Sub

Hello Brant:

I got it by changing

StoreColumn = Int((j - 4) / 7) * 7 + 3

StoreColumn = Int((j - 4) / 7) * 7 + 4

Thanks a lot

Riz

hello Brant

Works great, it does not pull Store Name in Column C.
Pls modify the code to accoplish that

Thanks

Tiz