none
Columns operation in VBA RRS feed

  • Discussion générale

  • Hi all,

    I come here after many attempts and unsuccessful tries. I have a work book of about 20 sheets, sheets 2 untill the end have the same columns names: “ Price”;”time”;”actual price”;”time”;”cost”;”time”;”actual cost”;”time”. Each of the sheets have a different number of data.

    My program is in 2 parts, first of all I want to insert a column called “Incremental Cost” between “actual cost “ and the last column. when calculating the incremental price I do the following, but the equation is wrong and I don’t know how to set the range as the length of the actual cost:

        Columns("H:H").Select

        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

        Range("H1").Select

        ActiveCell.FormulaR1C1 = "Incremental cost"

        Range("H2").Select

        ActiveCell.FormulaR1C1 = "=RC[-1]"

        Range("H3").Select

        ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"

        Range("H3").Select

        LastRow = Cells(Rows.Count, "H").End(xlUp).Row

        Selection.AutoFill Destination:= Range(“H3: H” & LastRow)

    The second part of this program consist of copying the columns “Actual price”, “incremental Cost”, and the last column “time” and copy them in sheet1. That I can do, what I cannot do is automate it for each sheet and make each sheet copy it next to the former’s one output. In my program I have to do it manually for each sheets and they keep overriding themselves.

    I have spent a lot of time trying to figure it out but was not able to, all help is appreciated.

    • Type modifié Aurel Bera mardi 4 juin 2013 07:28 Discussion
    lundi 3 juin 2013 20:55