Macro for moving data from worksheet to another but to the right dates

[Eyeson15]

Hello forum members,

I have a simple problem but I really really really need your assistance.

Before posting this new thread I have done extensive search on google for 'move column' 'move data' and couldn't find what I was looking for.

I have a workbook with 2 worksheets "Data" and "Ledger"

Every item in Data has a date and it needs to be moved to Ledger under the date specified.

Moving only column A:E into Ledger column A:E.

You will understand when you have a look so I attach screenshots.

I did the first three days manually to show what I am trying to achieve.

Any help will be greatly appreciated.

Regards,

James Leighon

 

[Eyeson15]

Does anyone have any suggestions??

 

[Eyeson15]

Sorry to bump. But I really need help with this matter. Anyone?

 

[Eyeson15]

Bump....

 

[Akuini]

Try this:
I assume that in sheet Ledger the header with the font bold is already in there. Otherwise this macro won't work.

Sub a926234a()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim ry As Long
Dim sDate
Dim rz As Long
Application.ScreenUpdating = False

Application.CutCopyMode = False
Set ws1 = sheets("Data")
Set ws2 = sheets("Ledger")

With ws1

    sDate = .Cells(1, 1)
    For i = 1 To .Range("A" & Rows.count).End(xlUp).row
        
        ry = .Range("A:A").Find(sDate, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        rz = ws2.Range("A:A").Find(sDate, SearchOrder:=xlByRows, SearchDirection:=xlNext).row + 1
        ws2.Range(ws2.Cells(rz, "A"), ws2.Cells(rz + ry - i, "E")).Value = _
        .Range(.Cells(i, "A"), .Cells(ry, "E")).Value
        sDate = .Cells(ry + 1, 1)
        i = ry
    Next i
End With
   
   Application.ScreenUpdating = True
   Application.CutCopyMode = True

End Sub

 

[Eyeson15]

Akuini

Thank you so so so much!

That works great. You have saved me from ALOT of work.

Can't thank you enough.

Cheers

James

 

[Akuini]

You're welcome. Glad to help.