0

I'm new to VBA coding (I mostly work with R) and I am having trouble writing a macro to copy and paste. Specifically, I can do the first command fine, but when I go to the next row to do the same thing, it reverts back to the initial cell.

Here's the code that I'm working with:

Keyboard Shortcut: Ctrl+h

Range("B2").Copy Range("C1")
Range("B4").Copy Range("C3")
Range("B6").Copy Range("C5")

And so on... This does exactly what I want it to do, but I was wondering if there is a way to generalize it so that I can highlight the first cell of each "block" that I wish to do this to (i.e., A1,A2,B1,B2,C1,C2 etc.) and just use the keyboard shortcut. I got it to work partially using:

 Sub Format()

' Format Macro

' Keyboard Shortcut: Ctrl+h

  Selection.Cut
  ActiveCell.Offset(-1, 1).Range("A1").Select
  ActiveSheet.Paste
  ActiveCell.Offset(1, -1).Range("A1").Select
  Selection.EntireRow.Delete

End Sub

But that always reverts back to the initial cell (A1 in this case) once I move on to the next block.

Thanks for your help!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Cody_15243
  • 13
  • 3
  • What do you want to do, copy or cut the active cell up 1 and 1 to the right? You should have a look at https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Mar 06 '19 at 20:47
  • I suppose it doesn't matter whether I copy or cut the cell and move it up 1 right 1 because I am ultimately removing the row that the cell was cut or copied from entirely. I assume that there's not that much of a difference between cutting and copying in terms of code (aside from the obvious cut vs. copy). I have a for loop written that gets rid of the rows after everything is copied, I just cant figure out how to do the initial copying or cutting. – Cody_15243 Mar 06 '19 at 20:51

1 Answers1

1

Something like this? This is a one-off operation, but could be broadened if there some broader pattern exists.

Sub x()

With ActiveCell
    .Offset(-1, 1).Value = .Value
    .EntireRow.Delete
End With

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26