copy data based on column header name and values in that header

[Ali Khan]

Hello,

I have many columns in worksheet with column headers in row1. I want to copy complete row along with column headers to sheet2 on the condition that the values in column header 'Total Balance' is above 0 and dates in column header 'Due Dates' is less than today.

Thanks,

-------------------------------------------------------------------------------
Mod Edit: Question moved to VBA section

 

[Asheesh]

Welcome to the forum..

Share the sample file with the required result

 

[Ali Khan]

Hello Asheesh,
Thank you very much. As required please find attached file.
It contains two sheets "Receivables" & "Due".
I want to copy from "Receivables" those rows to "Due" sheet where "Total Balance Amount" is > 0. Presently header 'Total Balance Amount' is in column K but not necessarily every time. So copying should first depend on column header and then values.
Thanks a lot.
Ali

 

[Nebu]

Hi:

Use the following code.

Sub test()
Application.ScreenUpdating = False

Dim rng As Range, fnd As Range

Set rng = Me.Range("A1:L1")
Set fnd = rng.Find(What:="Total Balance Amt")

If Not fnd Is Nothing Then
    j& = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
    Sheet2.Range("A1:L" & j).Clear
    rng.AutoFilter Field:=fnd.Column, Criteria1:=">0"
    i& = Me.Cells(Rows.Count, "A").End(xlUp).Row
    Me.Range("A1:L" & i).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
            Destination:=Sheet2.Range("A1")
End If
   
Application.ScreenUpdating = True

End Sub

Thanks

 

[Ali Khan]

Hi Neebu,

Thank you very much. It worked perfectly well. 'Me.' was giving error which i replaced by worksheet name. Similarly i changed sheet 2 in code to my required worksheet name.

Great help! Thanks again...Ali