How to copy the cell which has formula

[sugavanam]

Hi,

How to copy the cell which has formula by using macro

* I need to copy the 1500 cells in one column and paste into multiple times [20]

Attached sheet for referance...

1.The sheet is opening with 16 column
2.If i give input as 20, further 4 columns to be added [ Not affecting the column 'U' ]

Please guide me to solve the same.

-Sugavanam

[snb]

You can record a macro doing this....

menubar/edit/goto.../formulas

[arlu1201]

You can use this code. I have tested it, complete with the inputbox and even the columns get numbered accordingly.

Sub insert_cols()

lastcol = Range("A1").End(xlToRight).Value

If lastcol = "Total No" Then

    lastcol = Range("A1").End(xlToRight).Offset(0, -1).Value
    Range("A1").End(xlToRight).Select
    coladd = Left(ActiveCell.Address(1, 0), InStr(1, ActiveCell.Address(1, 0), "$") - 1)
    colprev = Left(ActiveCell.Offset(0, -1).Address(1, 0), InStr(1, ActiveCell.Offset(0, -1).Address(1, 0), "$") - 1)

End If

userno = InputBox("Please provide the total number of columns required")

coldiff = userno - lastcol

For i = 1 To coldiff

    Columns(colprev & ":" & colprev).Copy
    Columns(coladd & ":" & coladd).Select
    Selection.Insert shift:=xlToRight
    lastcol = lastcol + 1
    Range(coladd & "1").Value = Range(colprev & "1").Value + 1
    colprev = coladd
    coladd = Left(ActiveCell.Offset(0, 1).Address(1, 0), InStr(1, ActiveCell.Offset(0, 1).Address(1, 0), "$") - 1)
    
Next

End Sub

Thanks,
Arlette Aloysius
If this was useful then please rate it appropriately.

[Marcol]

Try this workbook

Run the macro "ColsRequired"

[arlu1201]

Marcol,

I have also tested the code you provided to see on how i can better my code. I observed that your code doesnt copy the formulae across to the new columns. That is what sugavanam wants.

[Marcol]

The question is, what formula?

If you are refering to =SUM(D4:R4), originally in T4, then I don't see the logic in this remaining unchanged if columns are added, as Column T is "Total No" surely this should total the row? ...

My logic says that this should actually be =SUM(D4:S4).

Not affecting the column 'U'

Column U doesn't have a formula, do you think sugavanam means Column T should remain unchanged?

Check the formula I have in "Total No" after you run the code.

P.S. the only other formulae I can see are =I552*#REF! and =J552*T560 in columns I & J, they look like mistakes to me.

For your benefit look at these rows of code as a way to avoid loops

        .Range("D1:E1").AutoFill rngNew, xlFillSeries
        .Cells(3, TotalColsRqd + 3 + 1).Resize(LastRow - 3, 1).Formula = "=SUM(" & rngNew.Offset(2, 0).Address(0, 0) & ")"