Copy daily cost entries into the particular ledgers
Solved/Closed
shanthich
Posts
5
Registration date
Monday December 29, 2014
Status
Member
Last seen
December 30, 2014
-
Dec 29, 2014 at 01:16 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 31, 2014 at 06:41 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 31, 2014 at 06:41 AM
Related:
- Copy daily cost entries into the particular ledgers
- Does it cost money to change your number - Guide
- Daily challenge akinator - Download - Casual
- Linkedin learning cost - Guide
- Kick sub cost - Guide
- Daily cash book software free download - Download - Billing and accounting
2 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Dec 30, 2014 at 03:42 AM
Dec 30, 2014 at 03:42 AM
Hello Shanthich,
Could you please upload a sample of your workbook so that we can see exactly what you would like to do. Show the input and expected output result.
You can use a file sharing site such as DropBox. Please desensitise any confidential data.
Regards,
vcoolio.
Could you please upload a sample of your workbook so that we can see exactly what you would like to do. Show the input and expected output result.
You can use a file sharing site such as DropBox. Please desensitise any confidential data.
Regards,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Dec 31, 2014 at 06:41 AM
Dec 31, 2014 at 06:41 AM
Hello again Shanthich,
Just a little addition to the code that I forgot about yesterday. Its up to you whether or not you use it. I've just added a simple sum formula to the "Total" column in the "Ledger" sheet which will calculate columns D & E for you. Only the values will be transferred to the various other sheets and the formulae will remain intact in the "Ledger" sheet after its contents are cleared. This is the amended code:-
You can also have a look at how it works in my test workbook here:-
https://www.dropbox.com/s/hnncnzpkxvazcrb/Shanthich%282%29.xlsm?dl=0
Again, its up to you whether you use it or not.
Kind regards,
vcoolio.
Just a little addition to the code that I forgot about yesterday. Its up to you whether or not you use it. I've just added a simple sum formula to the "Total" column in the "Ledger" sheet which will calculate columns D & E for you. Only the values will be transferred to the various other sheets and the formulae will remain intact in the "Ledger" sheet after its contents are cleared. This is the amended code:-
Sub TransferData()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim lrow As Long
Dim rng As Range
Set ws = Sheets("Ledger")
lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For Each rng In ws.Range("A2:A" & lrow)
Text = Left(rng.Value, 10)
Select Case Text
Case Is = "Milk"
rng.EntireRow.Copy
Sheets("Milk").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Case Is = "Vegetables"
rng.EntireRow.Copy
Sheets("Vegetables").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Case Is = "Misc."
rng.EntireRow.Copy
Sheets("Misc.").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Case Is = "Transport"
rng.EntireRow.Copy
Sheets("Transport").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Select
Next
ws.Range("A2:E" & Rows.Count).Rows.ClearContents
Beep
MsgBox "Data transfer complete!", vbExclamation
Application.ScreenUpdating = True
End Sub
You can also have a look at how it works in my test workbook here:-
https://www.dropbox.com/s/hnncnzpkxvazcrb/Shanthich%282%29.xlsm?dl=0
Again, its up to you whether you use it or not.
Kind regards,
vcoolio.
Dec 30, 2014 at 04:22 AM
This is the input data: that is fed in on a daily basis. This should be copied into separate sheets with the name of the 'Ledger' in Column A
Ledger Date Item No. of units Price/Unit Total
Milk 01/08/13 Milk 90 31.5 2835
Milk 02/08/13 Milk 10 34 340
Milk 03/08/13 Milk 5 34 170
Vegetables 01/08/13 Vegetables 20
Vegetables 02/08/13 Greenleaf 20
Vegetables 06/08/13 Onions 170
Misc. 03/08/13 Bread 53
Misc. 03/08/13 Bread 53
Transport 04/08/13 Auto Charges 100
Hope this is clear.
Dec 30, 2014 at 07:20 AM
I think this code may help you:-
Sub TransferData() Dim ws As Worksheet Dim lrow As Long Dim rng As Range Set ws = Sheets("Ledger") lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row For Each rng In ws.Range("A2:A" & lrow) Text = Left(rng.Value, 10) Select Case Text Case Is = "Milk" rng.EntireRow.Copy Sheets("Milk").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Case Is = "Vegetables" rng.EntireRow.Copy Sheets("Vegetables").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Case Is = "Misc." rng.EntireRow.Copy Sheets("Misc.").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Case Is = "Transport" rng.EntireRow.Copy Sheets("Transport").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) End Select Next ws.Range("A2:F" & Rows.Count).Rows.ClearContents Beep MsgBox "Data transfer complete!", vbExclamation End SubYou can have a look at my test workbook at this link:-
https://www.dropbox.com/s/eermk69exndncs5/Shanthich.xlsm?dl=0
to see if it is what you had in mind.
The code also deletes the input data in the "Ledger" sheet after it is transferred to the various other sheets as I assumed that you would not want to clutter up the "Ledger" sheet with used data.
Kind regards,
vcoolio.
Dec 30, 2014 at 11:05 AM
This is exactly what i am looking for. Thanks a lot :)
Regards,
Shanthich
Dec 30, 2014 at 07:37 PM
Glad that I could help.
BTW, another close one in the Third Test in Melbourne!
Cheers,
vcoolio.