Results 1 to 3 of 3

Thread: [VBA] How do I tell Excel to remember the current column?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by RockandGrohl; May 17th, 2019 at 10:11 AM.

  2. #2
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Posts
    196
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by RockandGrohl View Post
    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?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Have you tried setting it on fire?

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,008
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

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

    Look for the title "Tourref" in row 1

    Code:
        Title = "Tourref"
        Set f = Rows(1).Find(Title, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            Lastrowml = Cells(Rows.Count, f.Column).End(xlUp).Row
            Range(Cells(2, f.Column), Cells(Lastrowml, f.Column)).Copy Destination:=ct.Range("A3")
        Else
            MsgBox "lost title : " & Title
        End If
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •