How Can You Best Transfer Data from One Sheet to Another based on Headers

[cpyles]

Hello,

I am hoping someone could help teach me how to transfer data by columns from one sheet to another. Essentially, the headers on both sheets will be the same. However, the order of information on sheet 1 is never the same as the set order on sheet 2. I was wondering what is the best way to transfer all the data under (including blanks) from sheet one and put it under the correct corresponding header on sheet 2. There will not always be information in each row, however sheet two will always have the same order and headers. While sheet one will have the same header names, but not necessarily the same order or even all the headers on sheet 2. I am really new to creating custom function for excel, so any information you could pass along how to run this program would be very very appreciated. I am trying to surprise my team with a very needed help on this task, so info how to share this program would be helpful too !! Below is the set order and exact names for sheet 2:

[Header names separated by comma, comma not part of actual header name]
[There is a general header on both sheets (row 1), so these headers actually exist on row 2]

Action, Mnemonic Description, ACTIVITY_TYPE, RESULT_TYPE, ALPHA_RESPONSE, ALPHA_SEQUENCE, ALPHA_RESULT_VALUE, ALPHA_CONCEPT_CKI, ALPHA_TRUTH_STATE,ALPHA_GRID_DISPLAY, ALPHA_DEFAULT_IND, ALPHA_SOURCE_VOCABULARY,ALPHA_PRINCIPLE_TYPE, ALPHA_VOCAB_AXIS, ALPHA_CONTRIBUTOR_SYSTEM, ALPHA_LANGUAGE, DTA_NUMERIC_MAX,DTA_NUMERIC_MIN, DTA_NUMERIC_DECIMAL, MODIFIER_IND, FIRST_ALPHA_SINGLE_SELECT,WITNESS_REQUIRED, CODE_SET, EVENT_CD, EVENT_CD_DISPLAY, CONCEPT_CKI, DEF_TYPE, DEF_TEMPL,IO_FLAG, LOOK_BACK_MIN_RESULTS, LOOK_BACK_MIN_BMDI, LOOK_FORWARD_BMDI, Sex,AGE_RANGE, MINUTES_BACK , NORMAL_LOW,NORMAL_HIGH, FEASIBLE_LOW, FEASIBLE_HIGH, LINEAR_LOW, LINEAR_HIGH,CRITICAL_LOW, CRITICAL_HIGH, REVIEW_LOW, REVIEW_HIGH, DEFAULT_RESULT, UNITS_OF_MEASURE,ALPHA_CATEGORY_NAME, ALPHA_CATEGORY_SEQUENCE, ALPHA_CATEGORY_EXPAND_FLAG,BUILD_STATUS, ERR_REF_ROW_COL

 

[cpyles]

Hey it totally works great!. One small thing that was casuing the issue. Is there anyway you can have the program ignore the first row. The headers exist in row two on both sheet 1 and 2. On the sheets I work with row one was a paint fill for style so i need to just ignore it.

 

[cpyles]

By the way thank you again! This will save so much time for us all!

 

[mumps]

Give this a try:

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(2, srcWS.Columns.Count).End(xlToLeft).Column
    Dim header As Range, foundHeader As Range
    For Each header In srcWS.Range(srcWS.Cells(2, 1), srcWS.Cells(2, lColumn))
        Set foundHeader = desWS.Rows(2).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcWS.Range(srcWS.Cells(3, header.Column), srcWS.Cells(LastRow, header.Column)).Copy desWS.Cells(3, foundHeader.Column)
        End If
    Next header
    Application.ScreenUpdating = True
End Sub

 

[cpyles]

Perfect I apperciate you!

 

[mumps]

My pleasure.

 

[RedVines]

I am trying to do something similar...but I have three workbooks that I am transferring to one master sheet (So moving the data from sheet 1, sheet 2, sheet 3 all to sheet 4). The headers on sheet 1, 2, and 3 are not the same, but sheet 4 has headers that match individual columns on sheet 1-3. How would I update the code to pull from all three sheets?

 

[mumps]

Hello RedVines and welcome to the Forum. According to Forum rules, you should not post your question in another person's thread. Please start your own thread. If you send me a private message with a link to your new thread, I will have a look at it. Please review the Forum rules at this link: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html