PDA

View Full Version : Solved: Create 4 cell range to copy



wa1esy
05-19-2010, 03:21 AM
Sub fourcellcopy()
'
' fourcellcopy Macro
'
' Keyboard Shortcut: Ctrl+d
'
Range("J868:M868").Select
Selection.Copy
End Sub

So I want to make a quick little macro that allows me to use a keyboard shortcut to select the cell which i am in and the three cells immediately to the right, and then copy them. My only problem is that obviously in the above code selects a specific range, where as I want it to point to the cell I have highlighted at the time and the three adjacent ones. Can somebody just enlighten me as to how to do this please?

Many thanks.

GTO
05-19-2010, 03:34 AM
Try:
ActiveCell.Resize(, 4).Copy

wa1esy
05-19-2010, 03:47 AM
Ah yes thanks very much for that. I changed the 3 to 4 so that it grabbed the active cell AND the 3 cells to the left. Although obviously this can just be changed to whatever I suppose.

ActiveCell.Resize(, 4).Copy

This worked a treat though! I am a complete novice at VBA but hopefully everytime I ask a dumb question I'll learn something in the process until eventually I don't need to ask so many dumb questions! :thumb

Just out of interest if you wanted to specify cells above or below the active cell as well, what are the VBA codes for that kind of operation?

mdmackillop
05-19-2010, 05:39 AM
You can combine Offset and Resize to define a range
eg


ActiveCell.Offset(-3,2).Resize(5, 4).Copy