Option Explicit
Sub Step1()
Dim Original_ws As Worksheet
Dim Bank_ws As Worksheet
Set Original_ws = Worksheets("Original")
Set Bank_ws = Worksheets("Bank")
Bank_ws.Cells.Clear
Original_ws.Columns("A:I").Copy Bank_ws.Range("a1")
Dim a As Variant, b As Variant, c As Variant
Dim Fnd As Range
Dim i As Long, j As Long, k As Long, ini As Long
With Bank_ws
.UsedRange.UnMerge 'Unmerge cells in banksheet
Set Fnd = .Range("A:A").Find("Date", , , xlPart, xlByRows, xlNext, False, , False)
If Not Fnd Is Nothing And Fnd.Row > 1 Then ini = Fnd.Row + 2 Else ini = 1
a = .Range("A" & ini, .Range("I" & Rows.Count).End(3)).Value
End With
ReDim b(1 To UBound(a), 1 To 8)
For i = 1 To UBound(a) - 3
If Len(a(i, 7)) <> 0 Then
j = j + 1
b(j, 1) = i 'Line
b(j, 2) = a(i, 1) 'Date
b(j, 3) = a(i, 6) 'Vch Type
b(j, 4) = a(i, 7) 'Vch No.
b(j, 5) = a(i, 3) 'Particulars
b(j, 6) = a(i, 8) 'Debit
b(j, 7) = a(i, 9) 'Credit
ElseIf Len(b(j, 4)) <> 0 And Len(a(i, 1)) = 0 And b(j, 4) <> "None" Then
j = j + 1
Debug.Print a(i - 1, 7)
a(i, 1) = a(i - 1, 1) 'Date
a(i, 6) = a(i - 1, 6) 'Vch Type
a(i, 7) = a(i - 1, 7) 'Vch No.
b(j, 1) = i 'Line
b(j, 2) = a(i, 1) 'Date
b(j, 3) = a(i, 6) 'Vch Type
b(j, 4) = a(i, 7) 'Vch No.
b(j, 5) = a(i, 3) 'Particulars
b(j, 6) = a(i, 8) 'Debit
b(j, 7) = a(i, 9) 'Credit
Else
j = j + 1
b(j, 1) = i 'Line
b(j, 2) = a(i, 1) 'Date
b(j, 3) = a(i, 6) 'Vch Type
b(j, 4) = "None" 'Vch No.
b(j, 5) = a(i, 3) 'Particulars
b(j, 6) = a(i, 8) 'Debit
b(j, 7) = a(i, 9) 'Credit
End If
Next i
With Bank_ws
.Cells.Clear
.Range("A1:G1").Value = Array("Line", "Date", "Vch Type", "Vch No.", "Particulars", "Debit", "Credit")
.Range("A2").Resize(j, 7).Value = b
.Columns("F:G").NumberFormat = "0.00"
With .Cells
.EntireColumn.AutoFit
.Borders.LineStyle = xlNone
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
With .Font
.Bold = False
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With 'font
End With 'cells
.Range("A:A").NumberFormat = "General"
.Range("B:B").NumberFormat = "dd-mm-yyyy"
End With
With Bank_ws.Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub