Copy using active cell with VBA

kelsonbaird

New Member
Joined
Oct 14, 2019
Messages
7
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,089,272
Messages
5,407,313
Members
403,132
Latest member
Black_Mamba_1666

This Week's Hot Topics

Top