How to copy data from one sheet to another with given criteria

[kelly mort]

Given the sample image above, I want to copy the data under a given date unto a different sheet.

I will have the date in a textbox on a userform say txtDate in the format "dd-mm-yy".

So say I have 26-01-21 in the textbox, then I want to copy the data for that date as seen in image above.

Ignore those textbox labels on the image - they are not part of this post.

Thanks

 

[kelly mort]

    Dim iloc As Range, eloc As Range, lri&, j&, lre&
    Dim EXP As Worksheet, db As Worksheet, c&, i&, lr&
   
    
    Set db = Sheets("ACC")
    Set EXP = Sheets("EXPENSE")
       
    lr = EXP.Cells(Rows.Count, "A").End(xlUp).Row
    If lr < 4 Then lr = 4

Set iloc = EXP.Range("A4:A" & lr).Find(what:=CDate(otherDate), LookIn:=xlValues, lookat:=xlWhole)
Set eloc = db.Cells(Rows.Count, db.[A4].Column).End(xlUp).Offset(1)
eloc = CDate(iloc)
            c = 2
            
            For i = 1 To 15
                For j = 0 To 1
                    eloc.Offset(i, j) = iloc.Offset(i, j)
                Next j
                c = c + 2
            Next i

Initially I had this line which seems to work cool.

            For i = 1 To 15

Then I upgraded it this way and it also worked great while I have just a single date entered. But once I added extra dates, it fails to get the job done.

For i = 1 To Application.CountA(EXP.Range("A4:A" & lr)) - 2 '15

So instead of counting all the none blank rows or cells then subtracting 2 from it, I want to be able to count from the "iloc" cell until I meet the string "DAILY TOTALS" then subtract 2 from the number.

See image below:

 

[mohadin]

Hi
This just a demo
Go step by step

Sub test()
    Set myarea = Range("B5:B" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(2, 1).Areas
    For Each r In myarea
        r.Offset(-1, -1).Resize(r.Count + 1, 2).Select
    Next
End Sub

 

[kelly mort]

Ok cool.
It works cooler by selecting the records for a given date.

With the code just as it is, it selects the last date in the database and its records.

How do I use the variable "otherDate" to determine which date's record to select, where "otherDate" habours the date that I want to copy its data to the other sheet?

 

[mohadin]

Sub test()
Set myarea = Range("B5:B" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(2, 1).Areas
For Each r In myarea
If r.Offset(-1, -1).Resize(1) = "youdate" Then
r.Offset(-1, -1).Resize(r.Count + 1, 2).Select
End if
Next
End Sub

 

[kelly mort]

All set. Thanks a lot.

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe