VBA to match headers and copy data from below

[billionaire2003]

Hi everyone,

I am looking for some VBA code to help me with the following problem, it would be great if anyone could help.

I have two excel sheets, both with identical headers except they are in a different order. Sheet1 has data, Sheet2 is blank except for the headers.

In sheet2 I would like to find the column in sheet1 that has the corresponding header, and copy all the data in the column and paste into correct column on sheet2. I would like to do this for all columns in sheet2. For example, the column with header name 'Fruit Type' in sheet2. I would like to find the column in sheet1 with the header 'Fruit Type', and copy all the data in that column into the 'Fruit Type' column in sheet2.


Any help welcomed.

Thanks

 

[mumps]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lCol))
        Set foundHeader = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcWS.Range(srcWS.Cells(2, foundHeader.Column), srcWS.Cells(LastRow, foundHeader.Column)).Copy desWS.Cells(2, header.Column)
        End If
    Next header
    Application.ScreenUpdating = True
End Sub

 

[billionaire2003]

That works perfectly, thank you so much!

 

[mumps]

You are very welcome.

 

[Wongjiaxin]

What if the header is different but both headers have actually the same meaning? For example, the header in sheet1 is gender but sex in sheet2. i wanted to copy the data below gender to the sex colomn. Thanks if anyone could help.

 

[mumps]

Hello and welcome to the Forum. Please take a few minutes to read the Forum rules which state that you should not put your question in another member's thread. Please start your own new thread and include a link to this thread if you feel it is useful. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

 

[BellBell]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lCol))
        Set foundHeader = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcWS.Range(srcWS.Cells(2, foundHeader.Column), srcWS.Cells(LastRow, foundHeader.Column)).Copy desWS.Cells(2, header.Column)
        End If
    Next header
    Application.ScreenUpdating = True
End Sub

Hi, the VBA above is good to use. But what if i want to paste special?

 

[mumps]

This will paste values only.

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lCol))
        Set foundHeader = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcWS.Range(srcWS.Cells(2, foundHeader.Column), srcWS.Cells(LastRow, foundHeader.Column)).Copy
            desWS.Cells(2, header.Column).PasteSpecial xlPasteValues
        End If
    Next header
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[alexdi]

Can I ask about a variation of this? What if you want to match against a header and a value in a designated column?

Workbook Source, Table that might exist named A in a sheet with an unknown name:

User	GroupA​	GroupB​	IgnoreColumnZ​
bob	X​
jim		X​

Workbook Destination, Table named A:

Username	IgnoreColumnA​	IgnoreColumnB​	GroupA​	GroupB​
jim
suzy
bob

Desired result for Workbook Destination, Table A:

Username	IgnoreColumnA​	IgnoreColumnB​	GroupA​	GroupB​
jim				X​
suzy
bob			X ​	​

In Destination, the columns I want to copy from start after a certain known offset. Here, the offset is 3 and column 4 is the start. The name of the PK column (here, Username) would also be known. The background is that I'm trying to import from a workbook that's likely to be similar, but not quite identical to the target. (An earlier version of the target.)

The logic would be something like:

In Destination:

* Get first username
* Get name of first column after the offset

In Source:

* If a table with the same name exists, and if that combination of username and column name exist in the table, copy the value at the intersection

In Destination:

* Write the value to the intersection of the username and column name

Then:

* Repeat for all rows in that column
* Move to the next column, and repeat, until there are no more columns in the table

Data volume would be a maximum of 50K cells.

I've managed to hack something together that partially works, but it isn't very fast or reliable. Can you suggest a modification of your method?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your 2 sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).