Results 1 to 3 of 3

Thread: Copy using active cell with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy using active cell with VBA

    I would like to click on a cell then run a macro that would copy the cell to the left and 65 more cells below it to the active cell and the 65 below it. Anyone think of a good trick to do this? Basicly I want to click in cell N20 and have a macro that would copy M20:M85 into N20:N85, but I want to use that same macro to do the same thing with lets say Z20 (and Y20:Y85)... Any tricks would be great to hear

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copy using active cell with VBA

    Welcome to the Board!

    This should do it. I even added a few "checks" in it for you.
    Code:
    Sub MyCopyMacro()
    
    '   Check to make sure only one cell is selected
        If Selection.Count > 1 Then
            MsgBox "Please only select one cell"
            Exit Sub
        End If
    
    '   Make sure you are not in column A
        If ActiveCell.Column = 1 Then
            MsgBox "You cannot copy the column to the left if you are in column A"
            Exit Sub
        End If
        
    '   Copy range
        Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(65, -1)).Copy ActiveCell
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy using active cell with VBA

    Works perfect thanks.

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
  •