0

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.

Jerremy Leedham
  • 97
  • 4
  • 13
  • 3
    Turn on the macro recorder (bottom left usually), go through the steps you are describing. Then you can look at the macro, use F8 to step through it and see how it affects your sheet. Then I suggest reading up on [removing `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). That should get you pretty far. If you need help after this, post the code and whatever else you've tried. What have you searched for, anything? You should find myriad sources on VBA copy/paste. – BruceWayne Jan 20 '16 at 23:04

1 Answers1

2

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

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 1
    Thank you so much @BruceWayne! This was exactly what I was looking for. I ended up going with your code then for step three I used `Sub StepThree() Range("A1:AC1").Copy Range("A4:AC4")` Thanks again for your assistance it was more than generous :) – Jerremy Leedham Jan 21 '16 at 05:35