Option Explicit
Public Sub Format_Data()
Dim wsProcess As Worksheet
Dim rngLst As Range
Dim lngRow As Long, i As Long
'\\First delete sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Processed_Output").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'\\Copy original data sheet
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) 'Change Name Sheet1 if it is different for you
Set wsProcess = ActiveSheet
'\\Process Data
With wsProcess
.Name = "Processed_Output"
'\\Find out last row
Set rngLst = .Cells.Find("*", .Range("A1"), xlValues, xlPart, xlByRows, xlPrevious, False)
.Range(.Cells(1, "A"), rngLst).UnMerge
lngRow = rngLst.Row
'\\Copy data to new columns for rearrangement
'\\Check two specific columns B & C for words Batch No : & Return Reason :
For i = rngLst.Row To 1 Step -1
If .Cells(i, "C").Value = "Return Reason :" Then _
.Cells(i, "H").Copy .Cells(i, "AC")
If .Cells(i, "B").Value = "Batch No :" Then _
.Cells(i, "I").Copy .Cells(i, "AD")
Next i
'\\Now fill up blank rows with this copied data
With .Range("AC2:AD" & lngRow)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Columns(1).Value = .Columns(1).Value
End With
'\\Direct value method drops leading zeroes so loop through it
For i = lngRow To 2 Step -1
.Cells(i, "AD").Value = "'" & .Cells(i, "AD").Value
Next i
.Cells(2, "W").Cut .Cells(2, "V") 'Invoice Amount column label moved to right place
.Cells(2, "AC").Resize(1, 2).Value = Array("Return Reason", "Batch No") 'Assign header
'\\Delete unwanted columns
For i = .Cells(2, "AD").Column To 1 Step -1
If .Cells(2, i).Value <> "Invoice Amount" And _
.Cells(2, i).Value <> "Invoice" And _
.Cells(2, i).Value <> "Account No" And _
.Cells(2, i).Value <> "Return Reason" And _
.Cells(2, i).Value <> "Batch No" Then _
.Columns(i).Delete
Next i
'\\Delete unwanted rows
With .Range(.Cells(2, "A"), .Cells(lngRow, "E"))
.AutoFilter Field:=3, Criteria1:="="
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
'\\Show data and adjust column widths
.ShowAllData
.Columns.AutoFit
If .AutoFilterMode Then .AutoFilterMode = False
End With
End Sub
]. Check if it is possible for the software to generate standard report then you really don't need to do all this stuff in Excel.