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

Sub Test()
Dim ched, phed, lr As Long
Sheets("Sheet1").Select

'First Name Copy Paste Process
'---------------------------------------------------------------------------------------------
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"

'Last name Copy Paste Process
'---------------------------------------------------------------------------------------------
ched = Application.WorksheetFunction.Match("Last name", Sheets("Sheet1").Range("1:1"), False)
phed = Application.WorksheetFunction.Match("Last 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)
'---------------------------------------------------------------------------------------------

'country name Copy Paste Process
'---------------------------------------------------------------------------------------------
ched = Application.WorksheetFunction.Match("country name", Sheets("Sheet1").Range("1:1"), False)
phed = Application.WorksheetFunction.Match("country 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)
'----------------------------

End Sub

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Sub Test()
Dim wCopy As Worksheet, wPaste As Worksheet, iCol As Integer
Dim vMatch As Variant, lRw As Long

With ThisWorkbook
    Set wCopy = .Sheets("Sheet1")
    Set wPaste = .Sheets("Sheet2")
End With

Application.ScreenUpdating = False

With wPaste
    iCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, "A"), .Cells(Rows.Count, iCol)).ClearContents
End With

With wCopy
    For i = 1 To iCol
        vMatch = Application.Match(wPaste.Cells(1, i).Value, .Range("1:1"), 0)
        If IsNumeric(vMatch) Then
            lRw = .Cells(Rows.Count, vMatch).End(xlUp).Row
            .Range(.Cells(2, vMatch), .Cells(lRw, vMatch)).Copy wPaste.Cells(2, i)
        End If
    Next i
End With

Application.ScreenUpdating = True

End Sub

Check this file

Originally Posted by harman83

What did you do which I was missing while working on the code you provided? That will help me to learn from the mistakes.

I am not sure whats wrong at your end that's why I have uploaded the file with code