pull data from Sheet 1 to Sheet 2

[thanhson]

I would like to pull data from Sheet 1 to Sheet 2 of same excel file. Sheet 2 is the master sheet and it has 20 columns (headers are customer information such as firstname, lastname, phone etc.). Sheet 1 has some of the headers of Sheet 2. At the start Sheet 2 is empty (only has headers), and Sheet 1 has both headers and data.

I would like a macro that:
- Go through all column headers of Sheet 2.
- For each column header, copy the whole column (if exists) from Sheet 1 to corresponding column in Sheet 2.

Can someone help please tks in advance. I was inspired by the following post, but can't rewrite that . - http://en.kioskea.net/forum/affich-2...cified-columns


Example:
Sheet 2:

FirstName----LastName----Phone

Sheet 1:
Phone---------LastName
bb-------------cc
bb-------------cc
bb-------------cc


Desired result after executing the macro:
Sheet 2:

FirstName-------------Phone---------LastName
-------------------------cc-------------bb
-------------------------cc-------------bb
-------------------------cc-------------bb

Thanks all in advance!

[thanhson]

anyone helping out? tks.

[Mumps1]

Try:

Sub CopyCol()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Activate
    Dim lCol1 As Long
    lCol1 = Sheets("Sheet1").Range("IV1").End(xlToLeft).Column
    Dim colHeader As Range
    Dim foundVal As Range
    For Each colHeader In Sheets("Sheet1").Range(Cells(1, 1), Cells(1, lCol1))
        With Sheets("Sheet2").Rows(1)
            Set foundVal = .Find(what:=colHeader, _
            After:=.Cells(.Cells.Count), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
        If Not foundVal Is Nothing Then
            Columns(colHeader.Column).Copy Sheets("Sheet2").Cells(1, foundVal.Column)
        End If
        End With
    Next colHeader
    Application.ScreenUpdating = True
End Sub

[thanhson]

beautiful code Mumps1. works faultlessly. big thanks to you Mumps1.

[Mumps1]

My pleasure.