Updating Excel file, using values from other Excel file, using macro

KristelNulens 

<< Is this possible? >>
Yes. It is important to plan your work. Literally write a step-by-step plan for what the macro needs to do. You can write it as a set of VBA comment lines within your macro.

Important note: I'm assuming your column headings and column content do not involve an Excel table.

As you will see if you write most nontrivial macros (and other VBA procedures), you need to familiarize yourself (at least a little) with the classes and objects that Excel exposes, including Workbook objects (and the built-in collection object Workbooks), Worksheet objects (and the built-in collection object Worksheets), and Range objects.  (In technical terms, classes are like cookie cutters or clothing patterns, and objects created from them are the cookies or clothing.)  Full information is available in online Microsoft documentation.

But you can see some information -- the properties and methods for those and other objects -- in the Object Browser window (press F2 from the code editor to get there, or use the View | Object Browser menu item; then select Excel from the dropdown list). To see information for specific objects/properties/methods, you can right click their names and click on Definition in the popup menu that appears.

So for your item 1, you should define and use variables like these (the order is unimportant, and you are not required to use the three-letter prefixes that help you remember what the variables contain):

    Dim objExcelFile1   As Workbook
    Dim objExcelWksht1  As Worksheet
    Dim objExcelFile2   As Workbook
    Dim objExcelWksht2  As Worksheet
    '
    Dim strColHeading   As String

You should populate the object variables at the start of the macro (I have assumed that both workbooks are open; more code is needed if that is not true):

    '----   Assuming both Excel workbooks are open, load variables
    '       with references to their Workbook and relevant Worksheet
    '       objects.
    Set objExcelFile1 = Workbooks("Excel1.xlsx")
    Set objExcelWksht1 = objExcelFile1.Worksheets("DomainData")
    Set objExcelFile2 = Workbooks("Excel2.xlsm")
    Set objExcelWksht2 = objExcelFile1.Worksheets("ControlData")

Possibly have the user confirm (using the Name properties of those objects, and the MsgBox statement) that those are the correct workbooks and worksheets.

So what needs to be done to "pull up" values from Excel2's relevant worksheet? And what needs to be done to store those values in the other workbook? Both actions involve the Value property of a cell. So if the cell locations for the source cells and target cells never change, then the code would be similar to this:

    '----   Pull new column headings from Excel2.  Store them into
    '       Excel1.
    'If you are using A1 cell notation:
    strColHeading = objExcelWksht2.Range("B1").Value
    objExcelWksht1.Range("B3").Value = strColHeading
    '...or if you are using R1C1 cell notation:
    strColHeading = objExcelWksht2.Cells(1, 2).Value
    objExcelWksht1.Cells(3, 2).Value = strColHeading

That should be straightforward; repeat and adjust cell references as needed.

In your item 2, you refer to "a numerical value". I hope you mean that multiple numerical values are used (at least one per column); otherwise it would be extra effort to extract the relevant source and target column numbers/identifiers.

Also, the movement of columns could be tricky, because once the code moves a column, references to the remaining columns may be "incorrect". If you do not have a large number of columns, it may make sense to just make a copy of all the columns -- in the desired order -- to the right of the original data, and then delete the original columns.  Code that you might use for copying a single column and for deleting a single column:

    objExcelWksht1.Cells(1, 2).EntireColumn.Copy _
            Destination:=objExcelWksht1.Cells(1, 6)
...
    objExcelWksht1.Cells(1, 5).EntireColumn.Delete Shift:=xlShiftToLeft

At the end of your macro, it's good practice to clean up (destroy) any object references that you create:

    '----   Clean up: Destroy the object references (best to do so
    '       in the reverse of the order of creation).
    Set objExcelWksht2 = Nothing
    Set objExcelFile2 = Nothing
    Set objExcelWksht1 = Nothing
    Set objExcelFile1 = Nothing

 That should get you started.