Excel VBA filter: Copy/paste using offset
May 17, 2016 9:23 AM Subscribe
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!
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!
Even though teabag's answer looks great, here's an alternative version that does values only, without a loop:
posted by paper chromatographologist at 9:56 AM on May 17, 2016
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"
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
---
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:
posted by roosterboy at 11:36 AM on May 17, 2016 [1 favorite]
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
posted by bologna on wry at 11:41 AM on May 17, 2016
« Older Three Days in Lyon, 12 Hours in Paris | Finding work in New Zealand before having a visa... Newer »
This thread is closed to new comments.
posted by teabag at 9:47 AM on May 17, 2016