Extract data to another sheet layout

[Goalexcel]

Hello Expert. Please kindly help to find out the best way ,maybe with VBA to extract data to another sheet layout report .
DataSheet1 no always has the same range A21 to N

Below the link to check the excel file Mar Pav sent you 1 item

 

[Goalexcel]

@maabadi

 

[Goalexcel]

Sorry my fault. Try this:

Sub TransformData()

Dim i As Long, j As Long, Lr1 As Long, K As Long, F As Long, Lr2 As Long

For j = 1 To Sheets.Count Step 2
Lr1 = Sheets(j).Cells(Rows.Count, 1).End(xlUp).Row
' if you want more you can change 10 to number of Data group added to each sheet
For i = 1 To 10 Step 2
K = (i + 1) * 6 / 2
Sheets(j + 1).Cells(K, 1).Value = "DATA"
Sheets(j + 1).Cells(K, 3).Value = "DATA"
Sheets(j + 1).Cells(K, 6).Value = "DATA"
Sheets(j + 1).Cells(K, 8).Value = "DATA"
 
Sheets(j + 1).Cells(K, 2).Value = Sheets(j).Cells(20, i) 'Header B6
Sheets(j + 1).Cells(K, 7).Value = Sheets(j).Cells(20, i + 1) 'header G6

Sheets(j + 1).Cells(K + 1, 1).Resize(4).ClearContents
Sheets(j + 1).Cells(K + 1, 1).Value = Sheets(j).Cells(21, i)
Sheets(j + 1).Cells(K + 2, 1).Value = Sheets(j).Cells(22, i)
Sheets(j + 1).Cells(K + 3, 1).Value = Sheets(j).Cells(23, i)
Sheets(j + 1).Cells(K + 4, 1).Value = Sheets(j).Cells(24, i)

Sheets(j + 1).Cells(K + 1, 6).Resize(4).ClearContents
Sheets(j + 1).Cells(K + 1, 6).Value = Sheets(j).Cells(21, i + 1)
Sheets(j + 1).Cells(K + 2, 6).Value = Sheets(j).Cells(22, i + 1)
Sheets(j + 1).Cells(K + 3, 6).Value = Sheets(j).Cells(23, i + 1)
Sheets(j + 1).Cells(K + 4, 6).Value = Sheets(j).Cells(24, i + 1)

Next i
Next j

End Sub

Thank you Dear @maabadi for your cooperation , works like a charm!!!!

 

[maabadi]

You're Welcome & thanks for feedback.

 

[Goalexcel]

@maabadi can kindly ask you for help to make work with macro attached excel file

Laur Brown sent you 1 item

Tracking number2.xlsx

www.dropbox.com