Link to home
Create AccountLog in
Avatar of Conernesto
ConernestoFlag for United States of America

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?

X1      b      b      X2      b      b      X3      b      b      X4
100                  100                  100                  100
200                  200                  200                  200
300                  300                  300                  300
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If there is NO data in the blank columns then you would not need a macro

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.
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.Offset(0, i).PasteSpecial xlPasteFormulas
End Sub

Open in new window

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
Avatar of Conernesto


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

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