+ Reply to Thread
Results 1 to 2 of 2

Match Column Names based on Primary Key in Column A

  1. #1
    Registered User
    Join Date
    07-14-2014
    Location
    Stamford, CT
    MS-Off Ver
    2007
    Posts
    16

    Question Match Column Names based on Primary Key in Column A

    The following code works to update the target sheet based on matching column names within the source sheet.

    However, I would like to bolster this by not only updating based on matching field names, but also the primary key in column A. Meaning the data from the source sheet isn't always in the same order (based on primary keys) as the data in the target sheet. So I want to keep the order of the primary key in the first column and then update matching field names in column B through the last column where the primary keys match between the source and target worksheets.

    Does this make sense?

    Any assistance is appreciated....


    Sub CopyDataBlocks()

    'VARIABLE NAME 'DEFINITION

    Dim SourceSheet As Worksheet 'The data to be copied is here

    Dim TargetSheet As Worksheet 'The data will be copied here

    Dim ColHeaders As Range 'Column headers on Target sheet

    Dim MyDataHeaders As Range 'Column headers on Source sheet

    Dim DataBlock As Range 'A single column of data

    Dim c As Range 'a single cell

    Dim Rng As Range 'The data will be copied here (="Place holder" for the first data cell)

    Dim i As Integer

    'Change the names to match your sheetnames:

    Set SourceSheet = ThisWorkbook.Sheets("Source")

    Set TargetSheet = ThisWorkbook.Sheets("Target")

    With TargetSheet

    Set ColHeaders = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft)) 'Or just .Range("A1:C1")

    Set Rng = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) 'Shoots up from the bottom of the sheet untill it bumps into something and steps one down

    End With

    With SourceSheet

    Set MyDataHeaders = .Range("A1:C1")

    'Makes sure all the column names are the same:

    'Each header in Source sheet must have a match on Target sheet (but not necessarily in the same order + there can be more columns in Target sheet)

    For Each c In MyDataHeaders

    If Application.WorksheetFunction.CountIf(ColHeaders, c.Value) = 0 Then

    MsgBox "Can't find a matching header name for " & c.Value & vbNewLine & "Make sure the column names are the same and try again."

    Exit Sub 'The code exits here if thereäs no match for the column header

    End If

    Next c

    'There was a match for each colum name.

    'Set the first datablock to be copied:
    Set DataBlock = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)) 'A2:A & the last cell with something on it on column A

    'Resizes the target Rng to match the size of the datablock:

    Set Rng = Rng.Resize(DataBlock.Rows.Count, 1)

    'Copies the data one column at a time:

    For Each c In MyDataHeaders

    i = Application.WorksheetFunction.Match(c.Value, ColHeaders, 0) 'Finds the matching column name

    Rng.Offset(, i - 1).Value = Intersect(DataBlock.EntireRow, c.EntireColumn).Value 'Writes the values

    Next c

    'Uncomment the following line if you want the macro to delete the copied values:

    ' Intersect(MyDataHeaders.EntireColumn, DataBlock.EntireRow).ClearContents

    End With

    End Sub

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Match Column Names based on Primary Key in Column A

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    Please Login or Register  to view this content.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Match names in two separate column if = then pull info in 3 column
    By Bevg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-07-2013, 08:31 PM
  2. Replies: 5
    Last Post: 01-13-2012, 03:20 PM
  3. Replies: 4
    Last Post: 12-14-2009, 03:21 PM
  4. Count the Names in a Column based on another Column
    By realniceguy5000 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-30-2009, 02:42 PM
  5. Replies: 2
    Last Post: 03-16-2009, 12:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1