• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro Copy partywise and Datewise to new sheet

J

jack999

Member
I would like to copy party wise and date wise to new sheet with the help of macro.


Tax Exempted

Date Party Name Bill No. Amount

22/06/2012 Jackson company 3 11,573.48

22/06/2012 Primer associates 5 41,316.07

14/08/2012 Rital corporation 11 9,155.67

10/09/2012 Jackson company 19 10,329.02

14/12/2012 Primer associates 53 13,871.43

05/12/2012 Jerry and Bros 46 4,444.50

08/12/2012 Jimmy George 48 4,444.50

20/12/2012 Jerry Varghese 59 5,555.63

24/12/2012 Akash Associates 61 7,081.49

27/12/2012 Mithila Enter prises 66 6,888.98

20/10/2012 Akash Associates 29 10,230.40

Total = 114,660.77

Tax 12.5%

22/06/2012 Jerry and Bros 2 40,468.13

22/06/2012 Jimmy George 6 252,237.84

15/07/2012 Akash Associates 8 19,583.40

14/08/2012 Jackson company 10 223,666.27

24/08/2012 Primer associates 14 69,814.81

10/09/2012 Mithila Enterprises 18 11,093.00

19/09/2012 Primer associates 21 15,421.77

25/09/2012 Jimmy George 22 23,103.32

14/12/2012 Rital corporation 54 10,705.46

24/12/2012 Primer associates 63 5,688.90

Total = 671,782.90

5%

Date Party Name Bill No. Amount

22/06/2012 Jackson Company 1 42,976.45

22/06/2012 Akash Associates 4 186,794.92

15/07/2012 Jerry and Bros 7 171,726.50

14/08/2012 Jimmy George 9 81,371.71

18/08/2012 Jackson company 12 281,825.39

24/08/2012 Akash Associates 13 25,867.86

01/09/2012 Rital Corporation 15 35,224.27

02/09/2012 Primer associates 16 16,501.02

05/09/2012 Jimmy George 17 16,320.76

10/09/2012 Mithila Enter prises 20 17,174.13

End File
 
Hi Jack999,


Welcome to Chandoo forum.


You have posted good amount of data but the final result is little unclear i.e. where should it go like 2 separate sheets.


You can post a sample workbook with expected results.


How to post a sample workbook, see here:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
http://www23.zippyshare.com/v/68417631/file.html


I would like to copy to a new worksheet sorted by date wise, party wise but in different blocks.


Eg. Akash Associates, Tax Exempted block - copy all transaction to new sheet in the same workbook related to Akash Associates, of Tax Exempted.Then another name, until the tax exempted rage finish.


Same like 5% and 12.5 %


All result in one new sheet in the same workbook.
 
Thank you for your kind reply,

http://www73.zippyshare.com/v/43092846/file.html

Sheet 2 contains sample result (2)option.
 
1. Go to Visual Basic Editor

2. Insert a module using: Insert | Module

3. Paste the following code.

[pre]
Code: 
Public Sub ProcessData()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Delete old Processed Data Sheet and insert new one
On Error Resume Next
Sheets("Processed_Data").Delete
On Error GoTo 0
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Processed_Data"

With Sheets("Processed_Data")
For i = 1 To .Range("A" & Rows.Count).End(xlUp).Row
If Trim(.Range("A" & i).Value) = "" And .Range("B" & i).Value <> "" Then
.Range("B" & i).Copy .Range("E" & i)
Else
.Range("E" & i - 1).Copy .Range("E" & i)
End If
Next i

'Delete first row
.Rows(1).Delete

lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
With .Range("A1:E" & lLastRow)
'Delete unwanted items
.AutoFilter 1, ""
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter 1, "*Date*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter 1, "*End File*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter 1
End With

'Sort Data
lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
With .AutoFilter.Sort
With .SortFields
.Clear
.Add Sheets("Processed_Data").Range("B2:B" & lLastRow), _
xlSortOnValues, xlAscending, xlSortNormal
.Add Sheets("Processed_Data").Range("A2:A" & lLastRow), _
xlSortOnValues, xlAscending, xlSortNormal
End With
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
[/pre]

4. Select the sheet you want to process and then run Macro ProcessData.
 
You must log in or register to reply here.
Back
Top