Extract Multiple Rows into a Limited Template based on Multiple Criterias

[albertt]

I understand that this might be a long process, So I thought we can take it solve the intentions one by one and I would keep on editing the post

I have a data dump that I want to extract into a template and be printed out.

Here are my intentions:

[Initial][1]
1. From the data dump (sample above), is it possible to automatically extract the appropriate value into the Template with a maximum of 10 rows for each set of Template. [Template][2]


Each template would be either limited daily OR maximum of 10 transactions as long as they are from the same credit source.

2. Values for the templates are:
3. Credit Source = Source + Source Name
4. Total = All values inside the voucher
5. Account = Item Code
6. Detail = Item Name
7. Unit Code = Unit Code
8. Value = Total Debit

9. As the datadump is extensive, is it possible to have ~10 sets of template in 1 Sheet and once they are printed out individually, they are deleted?

10. And Finally, to colour code for those that have been extracted to the template
[Final][3]

Since I am new to VBA, I would have no issue with the inputs to their appropriate places and to colour code. But I am still learning about the loop function that I believe would be required for this?

Any help would be much appreciated!



Edit:

Here are the codes that I could come up with for now

    Sub Learn()
    Set wb = ThisWorkbook
    'Limiting No. Of Credits
    Set dtws = Worksheets("Datadump")
    Set wstr = Worksheets("trial")
    Dim vcdate
    vcdate = wstr.Cells(2, "B").Value
    Dim vcsource
    vcsource = wstr.Cells(2, "D").Value
    Dim NoE As Long
    With wstr.Cells(1, 1)
    .Value = Application.WorksheetFunction.CountIfs(dtws.Range("A:A"), vcdate, dtws.Range("J:J"), vcsource)
    NoE = wstr.Cells(1, 1).Value
    If NoE < 11 Then
    .Offset(, 2).Value = wstr.Cells(1, 1).Value
    Else
    NoE = 10
    .Offset(, 2).Value = NoE
    End If
    End With
    MsgBox NoE
    'End of Limiting No. Of Entries

    'Inputting Appropriately
    Set tempws = Worksheets("Template")
   
    With tempws
    .Cells(4, "J").Value = vcdate
    .Cells(6, "C").Value = vcsource


    End With

    For i = 1 To NoE
    If dtws.Cells(i + 1, 1) = vcdate And dtws.Cells(i + 1, 10) = vcsource Then

    With tempws
    .Cells(i + 9, 1) = dtws.Cells(i + 1, 2)
    'detail
    .Cells(i + 9, 4) = dtws.Cells(i + 1, 3) & " - " & dtws.Cells(i + 1, 5)
    'Unit Code
    .Cells(i + 9, 7) = dtws.Cells(i + 1, 6)
    'Value
    .Cells(i + 9, 9) = dtws.Cells(i + 1, 9)
   
    'Bottom Total Value
    .Cells(20, "I").Formula = "=sum(I10:I19)"
    .Cells(7, "C").Value = tempws.Cells(20, "I").Value

    End With
    i = i + 1
    End If
    Next


    'Create the new Voucher Sheet
 
    'Copy Values to Voucher Database
    Set tmpWB = Workbooks.Open(Path)
    wb.tempws.Range("C" & Rows.Count).End(xlUp).Row.Copy _
        Workbooks("VDB.xlsm").Sheets("Sheet1").Range("A2")
       
    
    End Sub

I believe I would not have an issue with the colour coding but it seems that the data extraction is the main issue...

Any help would be appreciated!

 

[albertt]

Hi all,

To provide better clarity, I have attached the Before and After that I did manually.

 

[albertt]

Purpose Clarification:

I have a data dump that I want to extract into a template and be printed out.

Here are my intentions:

From the data dump (sample above), is it possible to automatically extract the appropriate value into the Template with a maximum of 10 rows for each set of Template. And then colour-code those that have been printed to the Template.
These are the Template (Payment Voucher) limitations:

a. Each template contains the data from only 1 (one) day

If in 1st January 2020 & 2nd January 2020, there are 5 transactions /day, there would have to be 2 Templates (1 for each day).

b. Each Template should only be from 1 Source

So if in 1st January 2020 & 2nd January 2020, there are 5 transactions/day from each Source A & B, there would be 4 Templates (1 for each source/day).

c. Each template could only contain 10 lines.

So if in 1st January 2020 & 2nd January 2020, there are 11 transactions/day from each Source A & B, there would be 8 Templates (2 for each source/day).


2. Values for the templates are:
- Credit Source = Source + Source Name
- Total = All values inside the voucher
- Account = Item Code
- Detail = Item Name
- Unit Code = Unit Code
- Value = Total Debit