Maintaining a ledger book

[raj0007]

Hello everyone I need a Help from all pro to create excel sheet for maintaining a ledger book which picks the data from single sheet cash data entry. like

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD="align: right"]3/1/19[/TD]
[TD]cash[/TD]
[TD="align: right"]5200[/TD]
[/TR]
[TR]
[TD]3/1/19[/TD]
[TD]bansal 27[/TD]
[TD="align: right"]1700[/TD]
[/TR]
[TR]
[TD]3/1/19[/TD]
[TD]goyal 45[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]3/1/19[/TD]
[TD]nanu45[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]4/1/19[/TD]
[TD]mahinder[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]4/1/19[/TD]
[TD]vishal mohali ph1[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]5/1/19[/TD]
[TD]hemraj ph1[/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD]7/1/19[/TD]
[TD]satpal ph1[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD]8/1/19[/TD]
[TD]max 52[/TD]
[TD="align: right"]6700[/TD]
[/TR]
</tbody>[/TABLE]

all these entries to be sorted in the respective ledger account autofill. along with date. kindly help me out

 

[onlyadrafter]

Re: Need help to create a excel for maintaining a ledger book

Hello,

You need to be a bit more descriptive. Where is the source data? Where is there destination for the data? What are the rules determining the sort into respective accounts?

 

[raj0007]

Re: Need help to create a excel for maintaining a ledger book

hi sir the source of data is manual . and the destination of data is the sheet which is as per the column number 2 which belongs to the person account. and the sort is as per the column number 2 which is is the name of the account and copy the whole row and paste into the account sheet which can be renamed as per column no.2.

 

[raj0007]

Re: Need help to create a excel for maintaining a ledger book

can anyone help me to fix

 

[onlyadrafter]

Re: Need help to create a excel for maintaining a ledger book

Hello,

This code assumed column N is available. If not change to a different column.

Sub COPY_TO_SHEETS()
    Application.ScreenUpdating = False
    Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1"), Unique:=True
    MY_SOURCE = ActiveSheet.Name
    For MY_SHEETS = 1 To Range("N" & Rows.Count).End(xlUp).Row
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheets(MY_SOURCE).Range("N" & MY_SHEETS).Value
    Next MY_SHEETS
    Sheets(MY_SOURCE).Select
    For MY_SHEETS = 1 To Range("N" & Rows.Count).End(xlUp).Row
        Range("A1:C1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$C$" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:=Range("N" & MY_SHEETS).Value
        Range("A2:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        Sheets(Range("N" & MY_SHEETS).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Next MY_SHEETS
    Selection.AutoFilter
    Columns("N:N").ClearContents
    Application.ScreenUpdating = True
End Sub

 

[raj0007]

Re: Need help to create a excel for maintaining a ledger book

thanks alot sir for help let me figure out if I am able to implement it.

 

[raj0007]

Re: Need help to create a excel for maintaining a ledger book

Thanks alot sir for my help this is what I need exactly.. but sir I am not able to add further data it works only for the sample data which I shared

 

[raj0007]

Re: Need help to create a excel for maintaining a ledger book

Can any one help me to fixed the code. as it work on the final data if I add further data it is not reflecing that I need it will also filter automatically that from next row onwards so that whenever I add more data after putting this code it will also incluse that data I just only need to refresh that by clicking the button...Sub COPY_TO_SHEETS()
Application.ScreenUpdating = False
Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1"), Unique:=True
MY_SOURCE = ActiveSheet.Name
For MY_SHEETS = 1 To Range("N" & Rows.Count).End(xlUp).Row
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets(MY_SOURCE).Range("N" & MY_SHEETS).Value
Next MY_SHEETS
Sheets(MY_SOURCE).Select
For MY_SHEETS = 1 To Range("N" & Rows.Count).End(xlUp).Row
Range("A1:C1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:=Range("N" & MY_SHEETS).Value
Range("A2:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Sheets(Range("N" & MY_SHEETS).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Next MY_SHEETS
Selection.AutoFilter
Columns("N:N").ClearContents
Application.ScreenUpdating = True
End Sub

 

[onlyadrafter]

Re: Need help to create a excel for maintaining a ledger book

Hello,

How about deleting all the sheets first?

Sub COPY_TO_SHEETS()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For MY_SHEETS = Sheets.Count To 2 Step -1
        Sheets(MY_SHEETS).Delete
    Next MY_SHEETS
    Application.DisplayAlerts = True
    Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1"), Unique:=True
    MY_SOURCE = ActiveSheet.Name
    For MY_SHEETS = 1 To Range("N" & Rows.Count).End(xlUp).Row
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheets(MY_SOURCE).Range("N" & MY_SHEETS).Value
    Next MY_SHEETS
    Sheets(MY_SOURCE).Select
    For MY_SHEETS = 1 To Range("N" & Rows.Count).End(xlUp).Row
        Range("A1:C1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$C$" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:=Range("N" & MY_SHEETS).Value
        Range("A2:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        Sheets(Range("N" & MY_SHEETS).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Next MY_SHEETS
    Selection.AutoFilter
    Columns("N:N").ClearContents
    Application.ScreenUpdating = True
End Sub

Is this acceptable?

 

[raj0007]

Re: Need help to create a excel for maintaining a ledger book

Thanks alot sir for this help but when I refresh the data the row which I inserted in other sheets also get deleted . Is it possible to do ??