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]

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

Dropbox

www.dropbox.com

I have attached the link for the same

 

[Alex Blakenburg]

Data should be pasted from Row 3 (Row 1 is header and Row 2 is intentionally left Blank)

1) Leaving a blank row in a table anywhere other than at the bottom is a really bad idea, why do you feel you need to do that ?
2) The sample data your provided "does not" have a blank row in row 3.
Are you really going to have a blank row within the body of the data ?

i would like it to filter out specific category called "Debtors" and copy the contents of Column A,B,C,D,H

Filter out to me means Exclude but your sample workbook seems to indicate that you want only those lines containing the word Debtors, please confirm ?

Also have you considered a Power Query solution ?

 

[Haree]

Hello Sir Thank you for your time. Its does not have any particular reason sir. If that is not a very good idea we can exclude that part. we can go to data directly instead of blank row No sir I am very sorry i do not have a good knowledge in power query.

 

[Alex Blakenburg]

If you are happy to forgo having a blank row after the heading row give the below code a try.

The macro needs to figure out which sheets to include or which to exclude. At the moment I have gone with a list of sheets to Exclude.
Add the names of the sheets that you want to exclude separated by commas (do not add any additional spaces)
arrExclSht = Split("Receivables,Receivables Backup", ",")

Sub AppendAdvancedFilter()

    Dim shtRecv As Worksheet
    Dim shtSrc As Worksheet
    Dim arrHdgs As Variant
    Dim recvLC As Long, recvLR As Long
    Dim srcLR As Long, srcLC As Long
    Dim rngCrit As Range
    Dim rngDest As Range
    Dim rngSrc As Range
    Dim rngDestDel As Range
    Dim arrExclSht As Variant
    
    arrExclSht = Split("Receivables,Receivables Backup", ",")
    
    Set shtRecv = Worksheets("Receivables")
    With shtRecv
        .Range("A1").CurrentRegion.Offset(1).ClearContents
        arrHdgs = .Range("A1:F1").Value
        recvLC = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rngCrit = .Range(.Cells(1, recvLC + 2), .Cells(2, recvLC + 2))
        rngCrit(1).Value = "Category"
        rngCrit(2).Value = "Debtors*"

    End With
    
    For Each shtSrc In Worksheets
            
        ' Skip sheets in sheets to be excluded array
        If IsError(Application.Match(shtSrc.Name, arrExclSht, 0)) Then
            
                 With shtRecv
                    recvLR = .Cells(Rows.Count, 1).End(xlUp).Row
                    Set rngDest = .Range(.Cells(recvLR + 1, 1), .Cells(recvLR + 1, recvLC))
                    rngDest.Value = arrHdgs
                End With
            
                Set rngSrc = shtSrc.Range("A1").CurrentRegion
                
                rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
                
        End If
    Next shtSrc
    
    With shtRecv
        Set rngDest = .Range("A1").CurrentRegion
        rngDest.AutoFilter Field:=1, Criteria1:="Date"
        rngDest.Offset(1).Resize(rngDest.Rows.Count, 1).EntireRow.Delete
        rngDest.AutoFilter
    End With
    
    rngCrit.ClearContents

End Sub

 

[Haree]

Hello Alex really grateful for the help you did. The code works perfectly fine, thanks a ton for your kind help. I have a small doubt in the above code . I have asked for Debtors only but the code pulls out Debtors received as well. Its correct I don't want any change in the above. But if at any point of time I want it to exactly filter out "Debtors" alone kindly guide me on where i am supposed to change. Once again thank you so much for the help given.

 

[Alex Blakenburg]

I coded it to include anything "starting with" Debtors because you had Debtors Received in the output sheet of your sample file.

If you only want Debtors simply remove the asterisk ("*") from this line.

rngCrit(2).Value = "Debtors*"

Thanks for letting me know it worked for you. Glad I could help.

 

[Haree]

Thank you so much once again for your help sir. Really this saves a lot of time for me . Thanks

 

[Haree]

Hello Alex Sorry to trouble you I just have a small request the code is perfectly fine. A small help, the amount of the Debtors Received is also reflecting in Credit Amount, but it should reflect in Debit amount. Kindly excuse me and help me out. My bad i should have specified clearly.

 

[Alex Blakenburg]

Show me an example using data in your sample workbook and I will have a look tomorrow