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
Posts by albertd
-
-
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? -
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 ...
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:- 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
Code
Display MoreSub 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.
- Each template would be either limited daily OR maximum of 10 transactions as long as they are from the same credit source.
- Credit Source = Source + Source Name
- Total = All values inside the voucher
- Account = Item Code
- Detail = Item Name
- Unit Code = Unit Code
- Value = Total Debit
- 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?
- And Finally, to colour code for those that have been extracted to the template
like below.
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!