+ Reply to Thread
Results 1 to 5 of 5

Copy Columns from One Worksheet To Another Based on Matching Column Headers

  1. #1
    Registered User
    Join Date
    10-12-2024
    Location
    This is a site run by a bunch of fascists!
    MS-Off Ver
    Fascist Site!
    Posts
    16

    Copy Columns from One Worksheet To Another Based on Matching Column Headers

    Hello,

    I have a code that I wrote to copy data from one worksheet to another in the same workbook based on matching column header names between the two worksheets. My source data has 14 columns and my reconciled ('target") data has 10 columns. Between the two sets of data, there are eight columns total that match in terms of column header names. However, for some reason, my code is only matching two columns out of the possible eight between these two worksheets.

    Below is my code and attached is my sample workbook. Any help is much appreciated!


    [CODE]Sub TestData()

    Dim shSource As Worksheet, shReconcile As Worksheet
    Dim SourceLastCol As Long, SourceLastRow As Long, SourceRow As Range
    Dim Found1 As Range, Found2 As Range, j As Long, HeaderNameMatch As String

    Set shSource = Worksheets("SourceData")
    Set shReconcile = Worksheets("ReconData")

    With shSource

    SourceLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    For j = 1 To SourceLastCol
    HeaderNameMatch = .Cells(1, j).Value
    Set SourceRow = .Range("A1", .Cells(1, SourceLastCol))
    Set Found1 = SourceRow.Find(What:=HeaderNameMatch, lookat:=xlWhole, MatchCase:=False)

    If Not Found1 Is Nothing Then
    SourceLastCol = shReconcile.Cells(1, Columns.Count).End(xlToLeft).Column
    Set SourceRow = shReconcile.Range("A1", shReconcile.Cells(1, SourceLastCol))
    Set Found2 = SourceRow.Find(What:=HeaderNameMatch, lookat:=xlWhole, MatchCase:=False)

    If Not Found2 Is Nothing Then
    SourceLastRow = .Cells(Rows.Count, Found1.Column).End(xlUp).Row
    .Range(.Cells(2, Found1.Column), .Cells(SourceLastRow, Found1.Column)).Copy
    Found2.Offset(1, 0).PasteSpecial xlPasteAll
    End If
    End If
    Next j
    End With
    End Sub[CODE]
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: Copy Columns from One Worksheet To Another Based on Matching Column Headers

    Try this on a copy of your original wb
    Please Login or Register  to view this content.
    You forgot the forward slash in the last [/code] in Post #1

    Use Code Tags ExcelForum.JPG
    Last edited by jolivanes; 10-14-2024 at 01:39 AM.
    The inherent weakness of the liberal society: a too rosy view of humanity.

  3. #3
    Registered User
    Join Date
    10-12-2024
    Location
    This is a site run by a bunch of fascists!
    MS-Off Ver
    Fascist Site!
    Posts
    16

    Re: Copy Columns from One Worksheet To Another Based on Matching Column Headers

    Thank you! This worked exactly as intended! I see COUNTIF and resize being used in this code. Can you walk me through what these two functions are doing exactly?

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: Copy Columns from One Worksheet To Another Based on Matching Column Headers

    COUNTIF counts the number of cells in a range that meet the given criteria. So if it is more then 0, it means that that value exists.
    RESIZE enlarges the range, in this case a single cell, by a given amount. Here the amount is the calculated rows and columns.

    Thanks for the update and good luck.

    If any other, or more, questions, do not hesitate to ask.
    Last edited by jolivanes; 10-14-2024 at 02:15 AM.

  5. #5
    Registered User
    Join Date
    10-12-2024
    Location
    This is a site run by a bunch of fascists!
    MS-Off Ver
    Fascist Site!
    Posts
    16

    Re: Copy Columns from One Worksheet To Another Based on Matching Column Headers

    Ahhh, makes more sense now! Thank you again for all your help!

+ 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. [SOLVED] Transopse data by matching headers based on items in column & sheet name and merge amount
    By Maklil in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-12-2024, 05:53 AM
  2. Allocation of columns to other sheet based on matching headers
    By etaver87 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-24-2016, 09:44 AM
  3. Allocation of columns to other sheet based on matching headers
    By etaver87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2016, 09:41 AM
  4. Copy columns to other worksheet with same headers
    By etaver87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2016, 11:25 AM
  5. Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet
    By casper3043 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2015, 03:06 PM
  6. copy and paste columns based on column headers
    By patwary786 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2015, 11:43 PM
  7. Summary - Copy the columns based on column headers
    By rafiomeon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2012, 10:32 AM

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