Search code examples
excelvbaonedrive

Is it possible to restructure multi header one drive table using automate in one drive?

I am trying to restructure inside one drive using automate a function, but not sure if multi header table can be restructured. Thank you in advance for your help. enter image description here

I want to restructure it in this format -

Blockquote

Customer

Solution

  • You can loop through the dates in 1st row and create a nested loop which goes through the customers one by one and get the values according to the actual column and row. You should save the result on a new sheet.

    Sub Format_Table()
    lastRowOnNewFormatSheet = 2 'last empty row on newSheet
    'Loop through the columns with step 3
    For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column Step 3
        'Loop through the rows at every column
        For j = 3 To Cells(Rows.Count, 1).End(xlUp).Row
            Sheets("newFormat").Cells(lastRowOnNewFormatSheet, 1).Value = Sheets("oldFormat").Cells(j, 1) 'customer name
            Sheets("newFormat").Cells(lastRowOnNewFormatSheet, 2).Value = Sheets("oldFormat").Cells(1, i) 'date
            Sheets("newFormat").Cells(lastRowOnNewFormatSheet, 3).Value = Sheets("oldFormat").Cells(j, i) 'budget
            Sheets("newFormat").Cells(lastRowOnNewFormatSheet, 4).Value = Sheets("oldFormat").Cells(j, i + 1) 'actual
            lastRowOnNewFormatSheet = lastRowOnNewFormatSheet + 1 'update last empty row on newSheet
        Next j
    Next i
End Sub