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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. 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!


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:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top