Excel VBA filter: Copy/paste using offset

May 17, 2016 9:23 AM

Based on the active cell's position, I need to copy cells in the next column over and paste them a few columns to the left. Help?

I've already spent too much time fiddling with this stupid procedure, can someone possibly assist?

For simplicity purposes, say my cursor is currently in cell C1. I want to copy D1:D3 and paste it to A1:A3.

Caveat: I don't want to hard-code the cells, I want to use the offset property to select/copy the cells, and offset again(?) to paste. So at any time no matter which cell is active, it will jump to the right one column, copy the three cells down then jump over to the left three columns and paste.

There are no named ranges set up.

Thank you in advance!

posted by bologna on wry to Technology (5 answers total)

With VBA, just make a macro like this:

Public Sub CopyColumn()
  Dim i as Integer

  For i = 0 to 2
    ActiveCell.Worksheet.Cell(ActiveCell.Row + i, ActiveCell.Column - 2).Formula = ActiveCell.Worksheet.Cell(ActiveCell.Row + i, ActiveCell.Column + 1).Formula

  Next i
End Sub

You can also record a macro to do this using the arrow keys. The advantage to this is it will take all the formatting, etc. along with it.
posted by teabag at 9:47 AM on May 17, 2016

Even though teabag's answer looks great, here's an alternative version that does values only, without a loop:

Sub Macro1()
    Range(ActiveCell.Offset(0, -3), ActiveCell.Offset(0, -1)).Value2 = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Value2
End Sub

posted by paper chromatographologist at 9:56 AM on May 17, 2016

Response by poster: I appreciate both of your help.

---
teabag - your code gives me an error when I try to compile. It's highlighting the bolded part of the following with the error "Compile error: Method or data member not found"

ActiveCell.Worksheet.Cell(ActiveCell.Row + i, ActiveCell.Column - 2).Formula = ActiveCell.Worksheet.Cell(ActiveCell.Row + i, ActiveCell.Column + 1).Formula

I tried in vain to record the macro using the arrow keys, but it just hardcoded the cell references?

---
paper - man I'm so close after I tweaked your example a little bit! Only prob is I lose the cell formatting. Any way to copy/paste rather than just translate the value, so that the formatting stays intact?
posted by bologna on wry at 11:21 AM on May 17, 2016

Best answer: How about this:

Sub CopyCells()
    Dim sel As Range
    
    Set sel = Selection.Cells(1).Offset(0, 1).Resize(3)
    sel.Copy
    sel.Offset(0, -3).PasteSpecial xlPasteAll
End Sub

posted by roosterboy at 11:36 AM on May 17, 2016 [1 favorite]

Response by poster: roosterboy, yessss! Works like a charm! Thank you so much!
posted by bologna on wry at 11:41 AM on May 17, 2016