[VBA] How do I tell Excel to remember the current column?

[RockandGrohl]

I have a sheet where I want to copy the contents of a column titled "Tourref" to another sheet.

Tourref can appear in U, or maybe W, or T, etc.

What I've got is a loop that goes horizontally until it finds the Tourref column, but in the VBA It was like this:

Range("U2:U" & Lastrowml).Copy Destination:=ct.Range("A3")

And I need to do something more like this:

Range(activecell.column & 2 : activecell.column & Lastrowml).Copy Destination:=ct.Range("A3")

Does that make sense? I just need to tell it, wherever it is, get that column from 2 until the very end, whether it's U or T or V or whatever. Thanks!


EDIT: I have this, but it's selecting the entire range from row 2 downwards

c = ActiveCell.Column

Range(c & "2:" & c & Lastrowml).Copy 'Destination:=ct.Range("A3")

I just need it to select row 2 downwards in column U only (c=activecell.column correctly identifies column U as column 21)

 

[DataBlake]

I have a sheet where I want to copy the contents of a column titled "Tourref" to another sheet.

Tourref can appear in U, or maybe W, or T, etc.

What I've got is a loop that goes horizontally until it finds the Tourref column, but in the VBA It was like this:

Range("U2:U" & Lastrowml).Copy Destination:=ct.Range("A3")

And I need to do something more like this:

Range(activecell.column & 2 : activecell.column & Lastrowml).Copy Destination:=ct.Range("A3")

Does that make sense? I just need to tell it, wherever it is, get that column from 2 until the very end, whether it's U or T or V or whatever. Thanks!

include the lastrowml info
the dim and what you set it to.
also what column will always have a value in the last row?

 

[DanteAmor]

Look for the title "Tourref" in row 1

    [COLOR=#ff0000]Title [/COLOR]= "Tourref"
    Set f = Rows(1).Find([COLOR=#ff0000]Title[/COLOR], LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        Lastrowml = Cells(Rows.Count, f.Column).End(xlUp).Row
       [COLOR=#0000ff] Range(Cells(2, f.Column), Cells(Lastrowml, f.Column))[/COLOR].Copy Destination:=ct.Range("A3")
    Else
        MsgBox "lost title : " & Title
    End If