Posts by albertd

Hi Carim,

Yes, I believe I would be able to insert the Unique Identifier later on once I have grasped the loop printing.
But of course I would confirm with you again in the future

Hi Carim,

Thank you Carim!

The real one is actually categorised into different kind of transactions as well.
But I thought I would request for a help with the code and alter it into the different transactions afterwards.

Yes they have. But for the simpler version, Let's just use
YYYY/MM/XXXXX
XXXXX = Voucher Number

And, EBM

AHAHAHAHA! Thank you

Would you mind sharing the burden with the code string?

Quote from Carim

Hi,

There are many questions :

You mention Data dump ... so probably from a main frame ...

How many records are you dealing with approx ?

How many vouchers would that represent ?

What would be the frequency of this process ?

Why is your IT department not printing these thousands of vouchers ...?

Is printing only required for filing or for something else ?

Seems to me a little bit of common sense ... could really help ...

Display More

Hi Carim,

Here you go

How many records are you dealing with approx ?

Tens of Thousands and growing

How many vouchers would that represent ?

Tens of Thousands, I believe. Depends on the variations of the transactions. But would definitely be around that number.

What would be the frequency of this process ?

Everytime there is transaction actually. So I am cleaning up the backlogs...

Why is your IT department not printing these thousands of vouchers ...?

Newly established procedures...

Is printing only required for filing or for something else ?

Filing

Hi Carim,

Thank you very much for your reply.

It unfortunately is the requirement from management to print all the payment vouchers and save the database for all the vouchers or at least ensure that it can be retrieved any moment easily.

So I was thinking of putting more than 10 Vouchers per sheet while we are collecting the budget to move to access or sql.

Or do you perhaps have any suggestions?

Since I cant figure out how to edit...

Here are my intentions:

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. 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)**.

Hi all,

To further clarify my intentions, I have added Before and After that I did manually.

Dear All,This is what I have reached for now... But still could not do what it is supposed to do....Please do advise

Sub learn()
Set wb = ThisWorkbook


'Limiting No. Of Entries
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(template path*)
wb.tempws.Range("C" & Rows.Count).End(xlUp).Row.Copy _
Workbooks("VDB.xlsm").Sheets("Sheet1").Range("A2")


End Sub

Hello Carim!
Thank you very much for replying. Here is the sample workbook. Pardon me.

Would really appreciate if you could help!Sample.xlsm

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

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.

1. Each template would be either limited daily OR maximum of 10 transactions as long as they are from the same credit source.
2. Credit Source = Source + Source Name
3. Total = All values inside the voucher
4. Account = Item Code
5. Detail = Item Name
6. Unit Code = Unit Code
7. Value = Total Debit
8. 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?
9. And Finally, to colour code for those that have been extracted to the template

   like below.

Final

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!