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.
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
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$, VH, VA, VB, C%, 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 C = 1 To UBound(VH, 2) V = Application.Match(VH(1, C), VB, 0) If IsNumeric(V) Then V = Application.Match(VA(V, 1), .Item(1), 0) If IsNumeric(V) Then .Item("2:" & .Count).Columns(V).Copy Cells(2, C) 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 » !
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"?
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 …
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?
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.
Bookmarks