Hi,
Give this a whirl, your data format goes a bit funny halfway down so I wasn't sure what you wanted to display - I assumed that this hadn't been entered correctly.
To run this make sure there is nothing to the right of your data
Public Sub Kyle()
Dim rng As Range
Dim var
Dim lr As Long
On Error GoTo handler
Do
With Sheet1
Set rng = Range("A:A").Find("Total :").CurrentRegion
With rng
var = Array(.Cells(1, 1).Value, .Cells(1, 3).Value, .Cells(1, 5).Value, .Cells(1, 6).Value, .Cells(1, 7).Value)
End With
lr = Sheet1.Range("I65000").End(xlUp).Row + 1
.Range("I" & lr).Resize(rng.Rows.Count - 2, 5).Value = var
.Range("N" & lr).Resize(rng.Rows.Count - 2, 4).Value = rng.Offset(1, 0).Resize(rng.Rows.Count - 2, rng.Columns.Count - 3).Value
rng.ClearContents
End With
Loop
Exit Sub
handler:
End Sub
Bookmarks