Macro to Copy col form one wb2 sheets to wb1 sheet("Raw data") base on header value

[FaizanRoshan88]

Hi,
I try to copy specific header value columns form wb2 sheets to wb1 sheet("Raw Data"). What i done to browse scorce file & copy sheets Columns base on Header value to Master sheet("Raw Data"). I have code but its not work perfect. Please help me to get solution for this.

Sub Design()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim na As Object
Dim da As Object
Dim nu As Object
Dim it As Object
Dim qt As Object
Dim sp As Object
Dim am As Object
Dim cu As Object
Dim bi As Object
Dim bt As Object

      ' On Error GoTo Err_Execute
     
Set wb1 = ThisWorkbook
     
      FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xlsx (*.xlsx), *.xlsm (*.xlsm),*.xls (*.xls),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(fileName:=FileToOpen)

    For Each Sheet In wb2.Sheets

      With Sheet.UsedRange
     
        'Find "Name,Date,Num,Item,Qty,Sales Price,Amount & etc" in Row 1
        Set na = .Find("Name", lookat:=xlPart)
        Set da = .Find("Date", lookat:=xlPart)
        Set nu = .Find("Num", lookat:=xlPart)
        Set it = .Find("Item", lookat:=xlPart)
        Set qt = .Find("Qty", lookat:=xlPart)
        Set sp = .Find("Sales Price", lookat:=xlPart)
        Set am = .Find("Amount", lookat:=xlPart)
        Set cu = .Find("Customer", lookat:=xlPart)
        Set bi = .Find("Bill to", lookat:=xlPart)
        Set bt = .Find("Balance Total", lookat:=xlPart)
       
        'If found, copy the column to Sheet (Raw Data)
            Columns(na.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("G7")
            Columns(da.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("E7")
            Columns(nu.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("F7")
            Columns(it.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("H7")
            Columns(qt.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("L7")
            Columns(sp.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("M7")
            Columns(am.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("N7")
            Columns(cu.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("P7")
            Columns(bi.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("Q7")
            Columns(bt.Column).EntireColumn.Copy _
            Destination:=wb1.Sheets("Raw Data").Range("R7")
        End With
       
       
    Next Sheet

'Err_Execute:
'  If Err.Number = 0 Then MsgBox "Filter is created!" Else _
  '  MsgBox Err.Description
    End If
  Sheets("Interface").Activate
 
End Sub

 

[Chihiro]

Upload your sample workbooks (both source and destination).

With description of what isn't working on your code. And what the expected output should look like.

 

[FaizanRoshan88]

Hi Chihiro,
Thank for replay. Here i upload both file. wb1 ("Interface - copy") with sheet Interface that have control button & "Raw Data" sheet have destination of data. i have enter sample data in "Raw data" sheet. wb2 have source File with lot of sheets. What I try to use code browse wb2 "Source File 1" then select multiple sheets that contain required data ("exp. for this i need 'sale' & 'Balance' sheet data") after that code find the specified header name that mention in code, if column have same header name copy entire columns and paste in wb1 sheet("Raw Data") in special describe cells column.
In this code i don't know how to select multiple sheets for data & also its give error for copy & paste data. error 1004
Its this make sense to do like that in macro?