+ Reply to Thread
Results 1 to 11 of 11

Match and copy multiple columns based on different headers

  1. #1
    Registered User
    Join Date
    05-20-2019
    Location
    Romania, Buchares
    MS-Off Ver
    office 2016
    Posts
    6

    Match and copy multiple columns based on different headers

    Hello.
    I'm just a beginner in the VBA word. I love VBA because it can make my work so much easier.
    For the moment i have a small issue: i want to match and copy columns based on different headers. In one excel i have 2 sheets: Original data and Contract data. I have to match 40 something columns. Some of them have the same name, other have slightly different name and most of them have completely different names.

    What i have so far was found online and adapted :
    Sub A_02_Order_columnsONPREM()
    With Sheets("original data").Rows(1)

    Set t = .Find("Customer name", LookAt:=xlWhole)
    If Not t Is Nothing Then
    Columns(t.Column).EntireColumn.copy _
    Destination:=Sheets("Contract data").Range("a1")
    Else: MsgBox "Title Not Found"
    End If
    End With
    End sub

    This one is working just fine, the only problem is that the headers for the sheet Contract data are changed and moved quite frequently. Is there a way to define once a list with corresponding headers and copy the column based on that list?
    It is quite easy for me to make that list.I will make it once and update it when needed based on the name. Based on the column letter is more difficult because i have to find first the equivalent, see where it is...
    I will need to make e list like Customer name in Original data is equal to Name of customer in Contract data, Division in Original data is Continent in Contract data.

    Can you please help?
    Thank you
    Attached Files Attached Files
    Last edited by Flor87; 05-20-2019 at 08:31 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Match and copy multiple columns based on different headers


    Hi !

    First create your conversion table in a new worksheet then attach the workbook …

  3. #3
    Registered User
    Join Date
    05-20-2019
    Location
    Romania, Buchares
    MS-Off Ver
    office 2016
    Posts
    6

    Re: Match and copy multiple columns based on different headers

    Done, the name of the file is header names equivalents.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Match and copy multiple columns based on different headers


    In which workbook the code will be located ?

  5. #5
    Registered User
    Join Date
    05-20-2019
    Location
    Romania, Buchares
    MS-Off Ver
    office 2016
    Posts
    6

    Re: Match and copy multiple columns based on different headers

    I have uploaded a new file with the code inside. The code doesn't work correctly in this one.
    Unfortunately i cannot share the original file due to confidential data. In my file, the first thing that i do it to set insert the header in the row a1 from contract data (usually is a blank sheet) and they have the same name as in Original data. once the columns are copied i rename the header in the sheet contract data.
    As i've mentioned i'm just a beginner, i have a lot a ideas and no skills

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this demonstration !


    When a column stays blank so check the header names workbook …

    Paste this demonstration to the Contract data worksheet module :

    PHP Code: 
    Sub Demo1()
         
    Dim F$, VHVAVBC%, V
             F 
    ThisWorkbook.Path Application.PathSeparator "header names equivalents.xlsx"
             
    If Dir(F) = "" Then Beep: Exit Sub
             VH 
    Me.UsedRange.Rows(1).Value2
             Me
    .UsedRange.Offset(1).Clear
             Application
    .ScreenUpdating False
        With GetObject
    (F).Worksheets(1).UsedRange.Columns
             VA 
    = .Item(1).Value2
             VB 
    = .Item(2).Value2
            
    .Parent.Parent.Close False
        End With
        With Sheet1
    .UsedRange.Rows
            
    For 1 To UBound(VH2)
                    
    Application.Match(VH(1C), VB0)
                If 
    IsNumeric(VThen
                    V 
    Application.Match(VA(V1), .Item(1), 0)
                    If 
    IsNumeric(VThen .Item("2:" & .Count).Columns(V).Copy Cells(2C)
                
    End If
            
    Next
        End With
             Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  7. #7
    Registered User
    Join Date
    05-20-2019
    Location
    Romania, Buchares
    MS-Off Ver
    office 2016
    Posts
    6

    Re: Try this demonstration !

    It is working!Thank youuuuuuu so much!!!!!
    If it is not to much to ask...is it possible to have the headers equivalents defined in the code and get rid of the file "header name equivalents"?

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Match and copy multiple columns based on different headers


    The easy way is a settings worksheet as in case of any change you not have to mod the code !
    So if you do not want a specific workbook just add a worksheet in the main workbook where code is located.

    Or you can create the conversion arrays (VA, VB) totally by code with the Evaluate method or a VBA function like Array or Split …

  9. #9
    Registered User
    Join Date
    05-20-2019
    Location
    Romania, Buchares
    MS-Off Ver
    office 2016
    Posts
    6

    Re: Match and copy multiple columns based on different headers

    This file will be used by other users so i think the best way is to have a sheet inside the file. But i do not know how to make the changes. Can you please help me again?

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Match and copy multiple columns based on different headers


    Yes, later after you achieve your part and attach the final sample workbook like yet written since post #2 …
    Last edited by Marc L; 05-20-2019 at 08:26 AM.

  11. #11
    Registered User
    Join Date
    05-20-2019
    Location
    Romania, Buchares
    MS-Off Ver
    office 2016
    Posts
    6

    Re: Match and copy multiple columns based on different headers

    Done. I have created the file Automation file with 3 sheets. Original data, Contract data and Header name equivalents. I have inserted your code in the sheet Contract data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet
    By casper3043 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2015, 03:06 PM
  2. copy and paste columns based on column headers
    By patwary786 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2015, 11:43 PM
  3. [SOLVED] Copy pasting columns based on headers
    By krash297 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2013, 02:13 PM
  4. [SOLVED] Match, Copy and Paste Column Headers - varying number of columns
    By itcher in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2013, 10:49 AM
  5. Summary - Copy the columns based on column headers
    By rafiomeon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2012, 10:32 AM
  6. Match/Copy/Paste VBA with Multiple Headers and Files
    By Elwood07 in forum Excel General
    Replies: 0
    Last Post: 10-11-2011, 12:59 PM
  7. Excel 2007 : Macro to copy columns based headers
    By rls1316 in forum Excel General
    Replies: 2
    Last Post: 04-25-2011, 10:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1