Vba to copy data with col header name from ws1 to ws2

[FaizanRoshan]

I use this code to copy data from ws1 to other ws base of columns header. I need to find lot of columns header, in this code i repeat code for each col header data. what I need a code where i enter all header name & columns range (for paste) in one line to find and paste match complete columns in other ws.

[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]add After[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]count[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#303336]ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]
Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"RawData"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Activate
[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"RawData"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#858C93]'Find "Name,Date,Num,Item,Qty,Sales Price,Amount & etc" in Row 1[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] na [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Find[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Name"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] lookat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPart[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] da [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Find[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Date"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] lookat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPart[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] nu [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Find[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Num"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] lookat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPart[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] it [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Find[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Item"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] lookat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPart[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] qt [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Find[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Qty"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] lookat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPart[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] sp [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Find[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Sales Price"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] lookat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPart[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] am [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Find[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Amount"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] lookat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPart[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'If found, copy the column to Sheet (Filter Data)[/COLOR][COLOR=#303336]
        Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]na[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Column[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy _
        Destination[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"A1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]da[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Column[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy _
        Destination[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]nu[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Column[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy _
        Destination[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"C1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]it[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Column[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy _
        Destination[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"D1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]qt[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Column[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy _
        Destination[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"E1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]sp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Column[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy _
        Destination[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"F1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]am[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Column[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]EntireColumn[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy _
        Destination[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Filter Data"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"G1"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Else: MsgBox "Name Not Found"[/COLOR][COLOR=#303336] [/COLOR]</code>[COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]With[/FONT][/COLOR][COLOR=#242729][FONT=Arial]

 

[korhan]

How about something like this

Option Explicit


Sub Test()
    Dim arrFind As Variant
    Dim header As Range
    Dim i As Long
    arrFind = Array("Name", "Date", "Num", "Item", "Qty", "Sales Price", "Amount")
    
    For i = LBound(arrFind) To UBound(arrFind)
        With Sheets("RawData").Rows(1)
            Set header = .Find(arrFind(i), lookat:=xlPart)
                If Not header Is Nothing Then
                    Columns(header.Column).EntireColumn.Copy _
                    Destination:=Sheets("Filter Data").Range(Cells(1, i + 1))
                Else
                    MsgBox arrFind(i) & " not found!"
                End If
        End With
    Next i
End Sub

 

[korhan]

I am just trying to give you an idea. Maybe you can modify the code to your needs.

 

[FaizanRoshan]

Thank You for Help, Its Working Good.

I am just trying to give you an idea. Maybe you can modify the code to your needs.