macro to insert 1 column with formulas everytime it run

[aravindhan_31]

Hi,

I would appreicate verymuch if you guys help me on this.

When even i run a macro a new column has to be inserted with the same formulas of previous column to get the updated data.

I have attached a file with the detail explanation and examples

thanks again...

Regards
Arvind

[JBeaucaire]

The problem you're going to have is that if you leave the formulas in the columns, the current week column will updated its values even right up to the moment we run the macro. So I'm going to suggest that you use the formulas to collate the data and then immediately remove the formulas. This way when you run the macro, it gives you an unchanging picture of the moment you ran it.

Does that make sense?

[aravindhan_31]

Hi, thanks for your response,

It would really help me If I get a macro, as u said i will use the formula and remove.

Thanks

Arvind

[JBeaucaire]

Give this a try. Make sure the data on Sheet2 is ready to evaluate and click the button, it will add the new sections and static values.

If you want to leave the formulas, remove or comment out the

 .value = .value

...section of the macro.

[aravindhan_31]

Hi,

Thanks a lot its working... however I made some changes on the code to work on my data. Its really amazing

I have attached my data for your reference, When I run the macro everythings works fine except the Wk name, each time it gives me the same Wk. how do i change to get Wk1 Wk2 Wk3 etc.

and how do I add total of the results?

Thanks again for your help

Arvind

[JBeaucaire]

Go back and look at the Wk column in my uploaded sheet. To make incrementing Wks easy, I actually removed the alpha characters and formatted the cell with a custom number format and a formula. There's no actual text in that cell. The first Wk 1 cell is actually a formula. You could just do a COPY/PASTE of of my cell onto yours and it would take all the fun formatting with it.

[JBeaucaire]

Looking at your sheet, I think we can simplify down the formula insertion to one step, too. Since the sheet names match the column A values exactly, we can use an INDIRECT() formula for the match and just put them all in in one step.

Option Explicit

Sub WeeklyUpdate()
Dim LC As Long
Application.CutCopyMode = False
LC = Cells(2, Columns.Count).End(xlToLeft).Column
    
    If Cells(2, LC) = "Difference" Then
        Range(Cells(2, LC - 1), Cells(19, LC)).Copy Cells(2, LC)
        Range(Cells(3, LC + 1), Cells(18, LC + 1)).FormulaR1C1 = "=RC[-1]-RC[-2]"
        Range(Cells(3, LC), Cells(18, LC)).FormulaR1C1 = "=SUM(INDIRECT(RC1&""!$H:$H""))"
        
        With Range(Cells(3, LC), Cells(18, LC + 1))
            .Value = .Value
        End With
        
    Else
        Range(Cells(2, LC), Cells(19, LC)).Copy Cells(2, LC + 1)
        Range(Cells(2, LC), Cells(19, LC)).Copy Cells(2, LC + 2)
        Cells(2, LC + 2) = "Difference"
        Range(Cells(3, LC + 2), Cells(19, LC + 2)).FormulaR1C1 = "=RC[-1]-RC[-2]"
        Range(Cells(3, LC + 1), Cells(18, LC + 1)).FormulaR1C1 = "=SUM(INDIRECT(RC1&""!$H:$H""))"
        
        With Range(Cells(3, LC + 1), Cells(18, LC + 2))
            .Value = .Value
        End With
    End If
  
Range("A1").Select
End Sub

I also corrected an error in the second half's column-targeting.