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

 

[mumps]

Are you saying that there may be some headers on Sheet1 that are not on Sheet2? If that is the case, what would you want to do? Do you want to copy the entire column of data from Sheet1 to Sheet2 including blank cells? Does the data in all the columns in Sheet1 end at the same row?

 

[cpyles]

So sheet 2 will be a blank sheet with all the possible headers always in that same order ( The list above is the full list). While sheet one will have the data under the same header names filled out, but may not contain each header. And yes if there is blank data in a cell it should be transferred because the format and spacing of each cell in the columns should stay the same. Does this help?

 

[mumps]

The macro assumes that the data in Sheet1 ends at the sale row in each column.

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

 

[cpyles]

Hey let me give this a shot, thank you so much for your quick reply. I seriously appreciate your help!! I am not too sure what you mean by ending the column at the sale row. Is that a row / column name or an idea with in excel. I am new and trying to learn these programs. Whats the best way to integrate this in my sheet?

 

[mumps]

My apologies, that was a "typo". I meant to say "same row". Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. I hope this helps.

 

[cpyles]

I was able to run that program, but the output didn't quite work.

Ex.

A | B | C | D| (Sheet one) Starting
4 1 7 0
5 2 8 0
6 3 9 0

D | A | B | C (Sheet 2) Start
[ blank ]

After Running Above Program:

A | A | B | C
4
5 [rest blank]
6

Desired Output :

D | A | B | C
0 4 1 8
0 5 2 7
0 6 3 9

 

[mumps]

Are the header names in Sheet1 exactly the same as those in Sheet2?

 

[cpyles]

Yes I ran it with just doing ABCD (1) and DABC (2) for the header names no spaces or anything. The program seems to be replacing the header D in sheet two with A. The data under this A in sheet two is the correct data, but it should be in the second column in this example on sheet 2.

 

[mumps]

I tried it on a couple of dummy sheets and it worked properly. Can you post a screen shot of what your data looks like on both sheets? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.