Conernesto

asked on

# Excel Copy formulas from one column to another column

Hi, I want to copy the contents from Column X1 to X2, X3,X4 till the end of my last column. X1 starts in column F696 and it goes down to F1356. The last column is YJ. There are two empty columns between each X. Can this be down with a macro, a loop, or code?

conernesto.

X1 b b X2 b b X3 b b X4

100 100 100 100

200 200 200 200

300 300 300 300

conernesto.

X1 b b X2 b b X3 b b X4

100 100 100 100

200 200 200 200

300 300 300 300

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

It's much faster to copy values. Here's a macro to copy formulas, however:

```
Sub CopyX()
Dim i As Long, Start As Long, Finish As Long
Dim rg As Range
Application.ScreenUpdating = False
Start = Columns("F").Column
Finish = Columns("YJ").Column - Start
Set rg = Range("F696:F1356")
For i = 3 To Finish Step 3
rg.Copy
rg.Offset(0, i).PasteSpecial xlPasteFormulas
Next
End Sub
```

If all the formulas down column F are the same then I believe, although not tested that way, that this would be quicker than copying

Sub cpyColsWgaps()

Dim i As Integer

Dim trng As Range

Set trng = Range("F696:F1356").Offset(, 3)

For i = 6 To Range("YJ1").Column - 6 Step 3

Set trng = Union(trng, Range("F696:F1356").Offset(, i))

Next i

trng.FormulaR1C1 = Range("F696").FormulaR1C1

End Sub

Sub cpyColsWgaps()

Dim i As Integer

Dim trng As Range

Set trng = Range("F696:F1356").Offset

For i = 6 To Range("YJ1").Column - 6 Step 3

Set trng = Union(trng, Range("F696:F1356").Offset

Next i

trng.FormulaR1C1 = Range("F696").FormulaR1C1

End Sub

ASKER

Both solutions worked fine. I like the second solution because it also copies the formats.

conernesto.

conernesto.

You have selected a comment which is not a solution. Can you specify the serial number of the solution which is working for you?

Select the range F696:H1356 (3 columns)

Now at the bottom left corner of the selection there is a tiny black square.

Drag this black square as far right as necessary.