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

Question

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?

Answer 1

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.

Answer 2

This example selects a new Range of Cells defined by the current cell to a cell 5 to the right.

Note that .Offset takes arguments of Offset(row, columns) and can be quite useful.

---

Sub testForStackOverflow()
    Range(ActiveCell, ActiveCell.Offset(0, 5)).Copy
End Sub