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

cpyles

New Member
Joined
Jun 21, 2018
Messages
10
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
 
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.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Give this a try:
Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top