Excel – Simple VBA selection: Selecting 5 cells to the right of the active cell

excelexcel-2010vba

I am trying to select and a row of 5 cells to the right (the selection should include my active cell) of my active cell. My current code is: 

Sub SelectandCopy()
'
' SelectandCopy Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
ActiveCell.CurrentRegion.Select
Selection.Copy
Windows("Study.xlsx").Activate
End Sub

I do want not a specific range from my worksheet because I want to be able to make a selection of these data sets from anywhere within my worksheet. Any help would be greatly appreciated!!

Would you use the Selection.Extend?

Best Answer

This copies the 5 cells to the right of the activecell. If you have a range selected, the active cell is the top left cell in the range.

Sub Copy5CellsToRight()
    ActiveCell.Offset(, 1).Resize(1, 5).Copy
End Sub

If you want to include the activecell in the range that gets copied, you don't need the offset:

Sub ExtendAndCopy5CellsToRight()
    ActiveCell.Resize(1, 6).Copy
End Sub

Note that you don't need to select before copying.

Related Topic