• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy data from file 1 and paste in file 2 with matching headers

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I am trying to perform below mentioned Copy and Paste function with macro.
I have 2 different files, file 1 and file 2. I need to copy a range of 10 columns (for example) and paste it in file 2.

The problem is I need to match headers before pasting the data.
Say Column Header 'Bike Make' is at column B in file 1 and the same is at position column E in file 2.

Then I need to copy Column B of file 1 which is 'Bike Make' and paste in the Column E of the file 2.

I remember reading the solution earlier on the Forum itself but not could locate now.

Can anyone please help me in this.

Sorry for not uploading sample file.
 
Hi,

Below mentioned link seems to be working.

http://stackoverflow.com/questions/11890694/excel-vba-match-columns-while-pasting

Code: 
Sub MatchUpColumnDataBasedOnHeaders()
 
Dim wbk As Workbook
Set wbk = ThisWorkbook
Set ws = wbk.Sheets(1)
Set ws2 = wbk.Sheets(2)
Dim cell As Range
Dim refcell As Range
 
Application.ScreenUpdating = False
ws.Select
 
    For Each cell In ws.Range("A1:Z1")
 
        cell.Activate
        ActiveCell.EntireColumn.Copy
 
        For Each refcell In ws2.Range("A1:Z1")
            If refcell.Value = cell.Value Then refcell.PasteSpecial (xlPasteValues)
        Next refcell
 
    Next cell
Application.ScreenUpdating = True
 
End Sub


I was trying below link by @Hui Sir, but I was not able to understand/edit the macro.

http://forum.chandoo.org/threads/vba-macro-copy-and-paste-data-match-by-column-headings.22093/

Thanks. :)
 

Attachments

  • Copy.xlsb
    15.8 KB · Views: 9
With headers in destination worksheet,
very easy with AdvancedFilter method within a single codeline !​
Code: 
Sub Demo()
Sheet1.Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, , Sheet2.[A1:E1]
End Sub
 
Hi @Marc L , thanks a lot for the help.

I wil surely check your code and will revert if any issues.
Please correct me if I am wrong, advanced filter will work only in same file.

To be honest, I have never used advanced filter with VBA so I was bit scared.
Aslo, both the tables are in 2 different files.

PS - I guess, I can move the entire table from file 1 to file 2 on temp basis, this way I will be able to use your amazing 1 liner ! :)

Good night. :)
 
Last edited:
Hi @Marc L , kindly review below if you get time. Not urgent though.

While using your code I am getting bug as 'Run time error 1004 The extract range has a missing or illegal field name.'

Table 1 (from where I am copying data) has 36 header where as table 2 (destination) has 18 headers. Is this the reason for bug.

Sorry for not uploading sample file.

Kindly guide.
 

No issue if less headers
but destination headers must be written exactly same as source headers.

If you won't succeed, join sample workbooks with dummy data …

Training : with your post #5 attached workbook, what is the codeline
for only two columns in destination worksheet ?​
 
Last edited:
Hi @Marc L , thanks a lot for the help.

If I am not wrong it should be 'Sheet1.Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, , Sheet2.[A1:b1]' for 2 Columns.

I am re-checking the details, sure, will try to upload sample file.

Have a nice day ahead. :)
 
Hi @Marc L , thanks a lot for the help.

I also tried below mentioned code, it seems to be working.

Code: 
Dim Cell As Range
        Dim PasteCell As Range
      
        TempCol = TempSht.Range("bz1").End(xlToLeft).Column
        Set TempRng = TempSht.Range(TempSht.Cells(1, 1), TempSht.Cells(TempLR, TempCol))
       
        For Each Cell In TempRng
            Cell.Resize(TempLR).Copy
          
            For Each PasteCell In BAASheet.Range("A3:r3")
                If PasteCell.Value = Cell.Value Then PasteCell.PasteSpecial (xlPasteValues)
            Next PasteCell
        Next Cell

Will try the same with your code.
 
Hi,

There is a change in the code used by me.

"Set TempRng = TempSht.Range(TempSht.Cells(1, 1), TempSht.Cells(TempLR, TempCol))"
This line is replaced by

Updated line.
"Set TempRng = TempSht.Range(TempSht.Cells(1, 1), TempSht.Cells(1, TempCol))"

There was no need to use 'TempLR' in the code. This created problem.
Replaced by '1'.

Thanks. :)
 


According to future attachment and complementary information,
I maybe will do the same without any loop, all in one block statement …

​
 
You must log in or register to reply here.
Back
Top