How do I copy column where column header is "Testing"-CodePudding

I am new to VBA and am trying to copy the column from Row 2 onwards where the column header (in Row 1) contains a certain word- "Unique ID".

Currently what I have is:

Dim lastRow As Long
lastRow = ActiveWorkbook.Worksheets("Sheets1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheets1").Range("D2:D" & lastRow).Copy

But the "Unique ID" is not always in Column D

CodePudding user response：

You can try following code, it loops through first row looking for a specified header:

Sub CopyColumnWithHeader()

    Dim i As Long
    Dim lastRow As Long

    For i = 1 To Columns.Count
        If Cells(1, i) = "Unique ID" Then
            lastRow = Cells(Rows.Count, i).End(xlUp).Row
            Range(Cells(2, i), Cells(lastRow, i)).Copy Range("A2")
            Exit For
        End If
    Next

End Sub

CodePudding user response：

When you want to match info in VBA you should use a dictionary. Additionally, when manipulating data in VBA you should use arrays. Although it will require some learning, below code will do what you want with minor changes. Happy learning and don't hesitate to ask questions if you get stuck:

Option Explicit
    'always add this to your code
    'it will help you to identify non declared (dim) variables
    'if you don't dim a var in vba it will be set as variant wich will sooner than you think give you a lot of headaches
    
Sub DictMatch()
    'Example of match using dictionary late binding
    'Sourcesheet = sheet1
    'Targetsheet = sheet2
    'colA of sh1 is compared with colA of sh2
    'if we find a match, we copy colB of sh1 to the end of sh2
    
    '''''''''''''''''
    'Set some vars and get data from sheets in arrays
    '''''''''''''''''
        'as the default is variant I don't need to add "as variant"
        Dim arr, arr2, arr3, j As Long, i As Long, dict As Object
        
        'when creating a dictionary we can use early and late binding
        'early binding has the advantage to give you "intellisense"
        'late binding on the other hand has the advantage you don't need to add a reference (tools>references)
        Set dict = CreateObject("Scripting.Dictionary") 'create dictionary lateB
    
        dict.CompareMode = 1 'textcompare
        arr = Sheet1.Range("A1").CurrentRegion.Value2 'load source, assuming we have data as of A1
        arr2 = Sheet2.Range("A1").CurrentRegion.Value2 'load source2, assuming we have data as of A1
    
    '''''''''''''''''
    'Loop trough source, calculate and save to target array
    '''''''''''''''''
    'here we can access each cell by referencing our array(<rowCounter>, <columnCounter>
    'e.g. arr(j,i) => if j = 1 and i = 1 we'll have the values of Cell A1
    'we can write these values anywhere in the activesheet, other sheet, other workbook, .. but to limit the number of interactions with our sheet object we can also create new, intermediant arrays
    'e.g. we could now copy cel by cel to the new sheet => Sheets(arr(j,1).Range(... but this would create significant overhead
    'so we'll use an intermediate array (arr3) to store the results
            
    'We use a "dictionary" to match values in vba because this allows to easily check the existence of a value
    'Together with arrays and collections these are probably the most important features to learn in vba!
        For j = 1 To UBound(arr) 'traverse source, ubound allows to find the "lastrow" of the array
            If Not dict.Exists(arr(j, 1)) Then 'Check if value to lookup already exists in dictionary
                dict.Add Key:=arr(j, 1), Item:=arr(j, 1) 'set key if I don't have it yet in dictionary
            End If
        Next j 'go to next row. in this simple example we don't travers multiple columns so we don't need a second counter (i)
    
    'Before I can add values to a variant array I need to redim it. arr3 is a temp array to store matching col
    '1 To UBound(arr2) = the number of rows, as in this example we'll add the match as a col we just keep the existing nr of rows
    '1 to 1 => I just want to add 1 column but you can basically retrieve as much cols as you want
        ReDim arr3(1 To UBound(arr2), 1 To 1)
        For j = 1 To UBound(arr2) 'now that we have all values to match in our dictionary, we traverse the second source
            If dict.Exists(arr2(j, 1)) Then 'matching happens here, for each value in col 1 we check if it exists in the dictionary
                arr3(j, 1) = arr(j, 2) 'If a match is found, we add the value to find back, in this example col. 2, and add it to our temp array (arr3).
                'arr3(j, 2) = arr(j, 3) 'As explained above, we could retrieve as many columns as we want, if you only have a few you would add them manually like in this example but if you have many we could even add an additional counter (i) to do this.
            End If
        Next j 'go to the next row
    
    '''''''''''''''''
    'Write to sheet only at the end, you could add formatting here
    '''''''''''''''''
        With Sheet2 'sheet on which I want to write the matching result
            'UBound(arr2, 2) => ubound (arr2) was the lastrow, the ubound of the second dimension of my array is the lastcolumn
            '.Cells(1, UBound(arr2, 2)   1) = The startcel => row = 1, col = nr of existing cols   1
            '.Cells(UBound(arr2), UBound(arr2, 2)   1)) = The lastcel => row = number of existing rows, col = nr of existing cols   1
            .Range(.Cells(1, UBound(arr2, 2)   1), .Cells(UBound(arr2), UBound(arr2, 2)   1)).Value2 = arr3 'write target array to sheet
        End With
End Sub