(Solved) – copy paste the matched columns from one worksheet to another


I have a question about copy paste specific columns from one worksheet(ws_Copy) to another(ws_Dest). I need to loop through each column header and match it with the headers in ws_Copy. If it matches, I need to copy the whole column into the ws_Dest with the matched header.

Not sure if my explain is clear or not, here is the sample code:

Sub Import_data()
Dim path As String
Dim file As String
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lastcol As Integer
Dim lastrow_ir As Integer
Dim i As Integer
Dim m As Integer

path = ThisWorkbook.Sheets("MACROS").Range("import_path_IRHedge").Value
file = ThisWorkbook.Sheets("MACROS").Range("file_IRHedge").Value

'Set variables for copy and destination sheets
Set wsCopy = Workbooks(file).Sheets("data_ir_bpv")
Set wsDest = ThisWorkbook.Sheets("IR Hedge")

'Find last used column in the destination sheet
'Find last row in the copy sheet 
lastcol = wsDest.Range("C:AD").Column
lastrow_ir = wsCopy_ir.Range("C" & Rows.Count).End(xlUp).Row

'Use the Match function to find the corresponding column to copy paste
For i = 3 To lastcol
    Set Rng1 = wsCopy_ir.Range("C14:AC14")
    m = Application.WorksheetFuntion.Match(wsDest.Cells(1, i).Value, Rng1, 0)
    wsCopy_ir.Range("C14:C & lastrow_ir").Columns(m).Copy
    wsDest.Range("C2").Columns(i).PasteSpecial xlPasteValues

Next i
Application.CutCopyMode = False
MsgBox "Data has been imported successfully"
End Sub

But the error message “Object doesn’t support this property or method” kept showing up in the row:

 m = Application.WorksheetFuntion.Match(wsDest.Cells(1, i).Value, Rng1, 0)

Any help would be appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *