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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
416
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:

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

And I need to do something more like this:

Code:
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

Code:
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)
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
779
Office Version
2016
Platform
Windows
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:

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

And I need to do something more like this:

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,261
Office Version
2007
Platform
Windows
Look for the title "Tourref" in row 1

Code:
    [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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,286
Messages
5,449,457
Members
405,566
Latest member
JeIIyfish

This Week's Hot Topics

Top