CODEWITHSUNDEEP
vbaexcel
Jerremy Leedham
Jerremy Leedham

Reputation: 97

Excel VBA Copy-paste-move

I am only new to VBA coding so I don't have much of a clue of what I am doing unfortunately. I was hoping that I could get a hand here.

What I want to do is.

Step One: Copy a line (which has formulas) and paste it in the same position but as values only.
Step Two: Move a selection of cells down one line.
Step Three: Copy a line (with formulas) and past it on another line with the formulas.

This is all done on the same sheet.

If I could get some help with this or some direction to some tutorials that would be really appreciated.

Upvotes: 0

Views: 1136

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Normally I wouldn't do this without your showing some effort, but why not? The below are about as simple as I can get. Please still though, use the macro recorder to see how this all works. It's how a lot of us get started.

Sub StepOne()
Dim myRow As Long
myRow = 1 ' We will "copy/paste" on Row 1
' To avoid using the clipboard, you can simply set two ranges' values equal. This will
' clear out any formulas and leave the values.
Rows(myRow).Value = Rows(myRow).Value
End Sub

And the second:

Sub StepTwo()
Dim rng As Range
'Change this as required. Note the use of `Set` with a `Range` type.
Set rng = Range("A1:A10")
rng.Cut rng.Offset(1, 0)
End Sub

And the last one: 

Sub StepThree()
' Not tellin :P!  You should be able to get this. Hint: look at StepOne.  
End Sub

Edit: Ah, I realize now that Step Three is a little more involved than setting two ranges equal since you want to maintain the formula. However, I'll leave this as a learning opportunity for you to investigate. If you can't figure it out, let me know and I can guide you. :D

Upvotes: 2

Related Questions