Macro to Copy data from one sheet to another sheet if column headings match

Friends, I need your help to modify the macro

I need help to create Macro to Copy data from one sheet to another sheet if column headings match. In the below code when in the Headers column there is First Name column, it will copy the column from sheet 1 and paste the same under First Name column for sheet 2.

Please I need your help to modify the code and help me copy "Last name" and "country name" column data in the similar way from sheet 1 to sheet 2


Option Explicit
Sub Test()
Dim ched, phed, lr As Long
Sheets("Sheet1").Select
ched = Application.WorksheetFunction.Match("First Name", Sheets("Sheet1").RANGE("1:1"), False)
phed = Application.WorksheetFunction.Match("First Name", Sheets("Sheet2").RANGE("1:1"), False)

lr = Sheets("Sheet1").Cells(Rows.Count, ched).End(xlUp).Row

RANGE(Cells(2, ched), Cells(lr, ched)).Copy Sheets("Sheet2").Cells(2, phed)
Sheets("Sheet2").Select

RANGE(Cells(2, phed), Cells(Rows.Count, phed).End(xlUp)).NumberFormat = "DD/MM/YY"

End Sub

Thanks for the response Sixth sense...Really appreciated

I am facing the following issues-
Issue 1 - On running the code getting this error msg - Run Time error '9',Subscript out of range

And it points to this code row -
phed = Application.WorksheetFunction.Match("Country Name", Sheets("Sheet2").Range("1:1"), False)

Issue 2 - Last Name column data is same as First Name column data and Country Name column data is blank.

Please note - I have changed headers names in your code , hope I have done it correctly
ched = Application.WorksheetFunction.Match("Last name", Sheets("country name").Range("1:1"), False)
phed = Application.WorksheetFunction.Match("Last name", Sheets("country name").Range("1:1"), False)

to

ched = Application.WorksheetFunction.Match("Country Name", Sheets("Sheet1").Range("1:1"), False)
phed = Application.WorksheetFunction.Match("Country Name", Sheets("Sheet2").Range("1:1"), False)

Thanks for the response Sixth sense...Really appreciated

I am facing the following issues-
Issue 1 - On running the code getting this error msg - Run Time error '9',Subscript out of range

And it points to this code row -
phed = Application.WorksheetFunction.Match("Country Name", Sheets("Sheet2").Range("1:1"), False)

Issue 2 - Last Name column data is same as First Name column data and Country Name column data is blank.

Please note - I have changed headers names in your code , hope I have done it correctly
ched = Application.WorksheetFunction.Match("Last name", Sheets("country name").Range("1:1"), False)
phed = Application.WorksheetFunction.Match("Last name", Sheets("country name").Range("1:1"), False)

to

ched = Application.WorksheetFunction.Match("Country Name", Sheets("Sheet1").Range("1:1"), False)
phed = Application.WorksheetFunction.Match("Country Name", Sheets("Sheet2").Range("1:1"), False)

Mock Data sheet attachedColumn Data.xlsx

Sheet 1 column data needs to be copied to Sheet 2 with headers which matched with Sheet 1 headers. before running the code,sheet 2 has only headers

Thanks for the code..

On running the code getting this error msg - Run Time error '9',Subscript out of range
This is the line where code is pointing to debug...Set wCopy = .Sheets("Sheet1")

I followed the instruction givenColumn Data.xlsm

You are a ROCKSTAR..this file is working....What did you do which I was missing while working on the code you provided? That will help me to learn from the mistakes.