Transferring Data from Multiple Workbooks into One Master Workbook Using Headers

Status
Not open for further replies.

RedVines

New Member
Joined
Jun 22, 2018
Messages
3
I have three workbooks that I am trying to move into one by matching the headers (we’ll refer to the workbooks as WB1, WB2, WB3 and the new as the master sheet). Some information about the existing workbooks and the master sheet.
Master sheet is blank, except for the headers. Headers match headers used on WB1, WB2, and WB3, but may be in a different order.
Not all headers from WB1, WB2, and WB3 will be used on the mater sheet.
Some headers from WB1, WB2, and WB3 are duplicative, I would want to pull the data anyways but would not want to overwrite information from another workbook. I have plans to go back and dedupe once all of my data is transferred.
Some headers on WB1 do not match headers of WB2 or WB3 but do match some headers on the master sheet.
Some headers from WB2 do not match headers from WB1 or WB3, but do match some headers on the master sheet.
Some headers from WB3 do not match WB1 or WB2, but do match some headers on the master sheet.
Originally, I was going to try and use a index match or vlookup to pull the data, but my formula started to get messy. I saw there was a similar post yesterday that used the below, but I wasn’t sure how I could manipulate the code so it would work for me (I did copy the workbooks into various sheets on the master sheet).
(Credit to mumps)
Sub CopyCols()
Application.ScreenUpdating = False
Dim srcWS As Worksheet, desWS As Worksheet
Set srcWS = Sheets("Sheet1")
Set desWS = Sheets("Sheet2")
Dim LastRow As Long
LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim lColumn As Long
lColumn = srcWS.Cells(1, srcWS.Columns.Count).End(xlToLeft).Column
Dim header As Range, foundHeader As Range
For Each header In srcWS.Range(srcWS.Cells(1, 1), srcWS.Cells(1, lColumn))
Set foundHeader = desWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
If Not foundHeader Is Nothing Then
srcWS.Range(srcWS.Cells(2, header.Column), srcWS.Cells(LastRow, header.Column)).Copy desWS.Cells(2, foundHeader.Column)
End If
Next header
Application.ScreenUpdating = True
End Sub


Apologies if this was confusing and many many many thanks if you are able to assist.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Duplicate https://www.mrexcel.com/forum/excel...aster-workbook-using-headers.html#post5093073

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,122,991
Messages
5,599,242
Members
414,298
Latest member
bongielondy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top