Assuming your two sheets already exist and the headers are all there on the ENTRIES sheet, this will take data from row6 down on BANK FILE and paste into ENTRIES in the format you've demonstrated.
Option Explicit
Sub ReformatBankFile()
Dim ws1 As Worksheet, ws2 As Worksheet, MyARR As Variant
Dim NR As Long, LR As Long, Rw As Long
Set ws1 = Sheets("BANK FILE")
Set ws2 = Sheets("ENTRIES")
ws2.UsedRange.Offset(1).EntireRow.Clear
NR = 2
MyARR = Application.WorksheetFunction.Transpose(ws1.Range("C4:F4").Value2)
With ws1
LR = .Range("A" & .Rows.Count).End(xlUp).Row
For Rw = 6 To LR
ws2.Range("A" & NR).Resize(UBound(MyARR)).Value = MyARR
ws2.Range("B" & NR).Resize(4).Value = .Range("B" & Rw).Value
ws2.Range("D" & NR).Resize(4).Value = WorksheetFunction.Transpose(.Range("C" & Rw).Resize(, 4).Value)
ws2.Range("D" & NR + 2).Value = ws2.Range("D" & NR + 2).Value * -1
ws2.Range("D" & NR + 3).Value = ws2.Range("D" & NR + 3).Value * -1
NR = NR + 6
Next Rw
End With
End Sub
Bookmarks