filter and copy specific data

[Haree]

Hello All,
I have an excel sheet which has around 15 sheets, out of which three sheets would not be used for this particular purpose. In the remaining 12 sheets i have the following Columns starting from Column "A" respectively

Date

Category

Name

Particulars

SIN

Pieces

Weight

Debit Amount

Credit Amount

Old G

Old S

The column used for filtering is "Category" or 2nd Column. It has around 20 categories. But i would like it to filter out specific category called "Debtors" and copy the contents of Column A,B,C,D,H

and paste it to a different sheet which goes by the name receivables
Data should be pasted from Row 3 (Row 1 is header and Row 2 is intentionally left Blank)
The Destination worksheet has the following Columns starting from Column A Respectively.

Date

Category

Name

Particulars

Amount

Please Note : The Destination worksheet should be cleared of its contents expect for Headings and then paste the data every time i run the code And Category field for filtering data will have blanks at certain instances.

Any help would be greatly appreciated . Thanks for taking time to read this

 

[Haree]

New Microsoft Excel Worksheet.xlsx

This Excel file itself has sir. The debtors received value should reflect in a different column and the value of Debtors should reflect in a different column
Sorry for the trouble.

 

[Alex Blakenburg]

@Haree, OK so you need the Dr & Cr reversed for Category = "Debtors Received".

At the end of the code just before the End Sub add this:-

    ' For Category = Debtors Received swap debit and credit amounts
    Dim arrDest As Variant
    Dim i As Long
    Dim DrAmt As Variant, CrAmt As Variant          ' Used Variant to cater for empty cell
    arrDest = rngDest.Value
    
    For i = 2 To UBound(arrDest)
        If arrDest(i, 2) = "Debtors Received" Then
            DrAmt = arrDest(i, 5)
            CrAmt = arrDest(i, 6)
            arrDest(i, 5) = CrAmt
            arrDest(i, 6) = DrAmt
        End If
    Next i
    rngDest.Value = arrDest

 

[Haree]

Thank you so much sir . I am so happy you gave your time.

 

[Alex Blakenburg]

No problem. Happy to help.