Beginner macro programming question

[Guest]

Hi, I want to automate a simple task but know nothing about Excel macro
programming. The macro recorder doesn't do what I want so I guess I need to
write something.

I'd like to perform the following steps in a macro:

1. Cut the contents of the current cell
2. Move over 3 columns to the right
3. Paste the cut data.
4. Move back over to the original position and 1 row down.

That's it. I know, too easy? But I have no idea where to start. If
someone can get me started I can take it from there for the rest of my macro
needs.

Thanks for any help

Steve

 

[Guest]

How about:

Sub steve2()
With ActiveCell
.Copy .Offset(0, 3)
.Clear
.Offset(1, 0).Select
End With
End Sub

 

[Guest]

Hi Steve,

ActiveCell.Cut
ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -3).Select

 

[Guest]

Perfect! Thanks.

Side note.. why can't I replace the ".Copy" with ".Cut", thereby removing
the requirement to also perform the ".Clear"?

I tried it and it have no idea what it really did... it definitely didn't
work though

 

[Guest]

Actually you can use Cut. For example:

Sub steve()
ActiveCell.Cut
ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -3).Select
End Sub

will do the same thing. This version requires 2 Selects - one to go to the
paste cell and one to get back. Rumor has it that Selects make macros slow.
I don't think it really matters here. Either code will do.

 

[Gary Keramidas]

another way:
should be able to do it with 2 lines of code

ActiveCell.Cut Destination:=ActiveCell.Offset(0, 3)
ActiveCell.Offset(1).Select

 

[Guest]

Hi all,

Using the ActiveCell method will mean that your starting cell need to be in
the current cell position where the data is, what if I want to move the data
but I am
not in my starting cell position.

I think we can use the Range method but not sure how to write the codes
Thanks

 

[ilia]

Is this what you want? Will cut the first cell in selection,
regardless of active cell, and move it three columns over.


Public Sub myMacro()
With Selection.Cells(1)
.Cut .Offset(0, 3)
End With
End Sub

 

[Guest]

Thank for looking into this. I think I didn't made myself clear on this.
Using the code by Gary, it only works if you click on the cell with data,
assuming that I have data in col A,B and C and row 1 and 2. If I have click
on a blank anywhere in the sheet and run this code, its will not work because
I am not in cell A1.

How to re-write this code for its to run if I am anywhere in the sheet but
cell A1