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

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?

This question is related to vba excel excel-2010

The answer is


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.


Similar questions with vba tag:

Similar questions with excel tag:

Similar questions with excel-2010 tag: