CODEWITHSUNDEEP
vbaexcelexcel-2010
user2437803
user2437803

Reputation: 77

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?

Upvotes: 3

Views: 114670

Answers (2)

Jon Crowell
Jon Crowell

Reputation: 22348

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.

Upvotes: 10

enderland
enderland

Reputation: 14165

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

Upvotes: 2

Related Questions